Django – limiting query results

Django – limiting query results

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?

Solutions/Answers:

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:

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.

References