Django - limiting query results

Question

I want to take the last 10 instances of a model and have this code:

 Model.objects.all().order_by('-id')[:10]

Is it true that firstly pick up all instances, and then take only 10 last ones? Is there any more effective method?

Answer 1

Django querysets are lazy. That means a query will hit the database only when you specifically ask for the result.

So until you print or actually use the result of a query you can filter further with no database access.

As you can see below your code only executes one sql query to fetch only the last 10 items.

In [19]: import logging                                 
In [20]: l = logging.getLogger('django.db.backends')    
In [21]: l.setLevel(logging.DEBUG)                      
In [22]: l.addHandler(logging.StreamHandler())      
In [23]: User.objects.all().order_by('-id')[:10]          
(0.000) SELECT "auth_user"."id", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."password", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."is_superuser", "auth_user"."last_login", "auth_user"."date_joined" FROM "auth_user" ORDER BY "auth_user"."id" DESC LIMIT 10; args=()
Out[23]: [<User: hamdi>]

Answer 2

Actually I think the LIMIT 10 would be issued to the database so slicing would not occur in Python but in the database.

See limiting-querysets for more information.

Answer 3

Looks like the solution in the question doesn't work with Django 1.7 anymore and raises an error: "Cannot reorder a query once a slice has been taken"

According to the documentation https://docs.djangoproject.com/en/dev/topics/db/queries/#limiting-querysets forcing the “step” parameter of Python slice syntax evaluates the Query. It works this way:

Model.objects.all().order_by('-id')[:10:1]

Still I wonder if the limit is executed in SQL or Python slices the whole result array returned. There is no good to retrieve huge lists to application memory.

Answer 4

Yes. If you want to fetch a limited subset of objects, you can with the below code:

Example:

obj=emp.objects.all()[0:10]

The beginning 0 is optional, so

obj=emp.objects.all()[:10]

The above code returns the first 10 instances.

Answer 5

Slicing of QuerySets returns a list which means if you do like:

>>> Model.objects.all().order_by('-id')[:10]

it will return a list and the problem with that is you cannot perform further QuerySet methods on list

So if you want to do more on the returned results, you can do something like:

>>> limit = 5 # your choice
>>>
>>> m1 = Model.objects.filter(pk__gte=Model.objects.count() - limit) # last five
>>> m2 = Model.objects.filter(pk__lte=limit)  # first five

Now you can perform more methods:

# Just for illustration
>>> m2.annotate(Avg("some_integer_column")) # annotate
>>> m2.annotate(Sum("some_integer_column"))
>>> m2.aggregate(Sum("some_integer_column")) # aggregate

By using slice notation([]) to limit results, you may also limit the ability to chain QuerySet methods.

If you are pretty sure that you will not need to make any further query then slicing will do the thing.

Answer 6

The simple answer for filter issue

Notification.objects.filter(user=request.user).order_by("-id")[:limit]

Just put order_by and then [:limit]

Answer 7

As an addition and observation to the other useful answers, it's worth noticing that actually doing [:10] as slicing will return the first 10 elements of the list, not the last 10...

To get the last 10 you should do [-10:] instead (see here). This will help you avoid using order_by('-id') with the - to reverse the elements.

Answer 8

Django 1.9.1

print qs.query and see LIMIT

pos_random = Position.objects.filter( gps_time__gte = start_time, gps_time__lte = end_time, vehicle = v)[:**LIMIT_NUMBER**]