Specifying limit and offset in Django QuerySet wont work

django-filter example
django-filter foreign key
django queryset get field value
django queryset limit
django queryset to list
django get related objects
django iterate over queryset
django annotate

I'm using Django 1.6.5 and have MySQL's general-query-log on, so I can see the sql hitting MySQL. And I noticed that Specifying a bigger limit in Django's QuerySet would not work:

>>> from blog.models import Author  
>>> len(Author.objects.filter(pk__gt=0)[0:999])
>>> len(Author.objects.all()[0:999])

And MySQL's general log showed that both query had LIMIT 21.

But a limit smaller than 21 would work, e.g. len(Author.objects.all()[0:10]) would make a sql with LIMIT 10.

Why is that? Is there something I need to configure?

It happens when you make queries from the shell - the LIMIT clause is added to stop your terminal filling up with thousands of records when debugging:

You were printing (or, at least, trying to print) the repr() of the queryset. To avoid people accidentally trying to retrieve and print a million results, we (well, I) changed that to only retrieve and print the first 20 results and print "remainder truncated" if there were more. This is achieved by limiting the query to 21 results (if there are 21 results there are more than 20, so we print the "truncated" message). That only happens in the repr() -- i.e. it's only for diagnostic printing. No normal user code has this limit included automatically, so you happily create a queryset that iterates over a million results.

(Source)

Making queries | Django documentation, Django doesn't hit the database until you explicitly call save() . The save() You can stack filters together all day long, and Django won't actually run the query until the QuerySet is evaluated. This is the equivalent of SQL's LIMIT and OFFSET clauses. The field specified in a lookup has to be the name of a model field. It's almost certainly not what the caller intended and it's not going to work as a way to count how many things were returned after the limit and offset are applied. LIMIT/OFFSET are applied after the result set is determined (and in this case it consists of one item, so it's pointless). comment:3 Changed 13 years ago by cheap_hydrocodone

Django implements OFFSET using Python’s array-slicing syntax. If you want to offset the first 10 elements and then show the next 5 elements then use it

MyModel.objects.all()[OFFSET:OFFSET+LIMIT]

For example if you wanted to check 5 authors after an offset of 10 then your code would look something like this:

Author.objects.all()[10:15]

You can read more about it here in the official Django doc

QuerySet API reference | Django documentation, To make this work, most QuerySet methods return new querysets. If a query doesn't have an ordering specified, results are returned from the database in For example, most databases don't allow LIMIT or OFFSET in the combined queries. SELECT score FROM assess_exam WHERE assessment_id = 5 ORDER BY score LIMIT 690,1 But if possible, I'd like to stay within Django's ORM. Mostly, it's just bothering me that I can't seem to use order_by with a filter and a limit. Any ideas?

for offset and limit i used and worked for me :)

MyModel.objects.all()[offset:limit]

for exapmle:-

Post.objects.filter(Post_type=typeId)[1:1]

python, i'm using django 1.6.5 , have mysql's general-query-log on, can see sql hitting mysql. , noticed specifying bigger limit in django's queryset not work: 24 Specifying limit and offset in Django QuerySet wont work Jun 4 '14 9 Strange blocking behaviour with gevent/grequests over HTTPS Mar 27 '16 8 Can I use more than 26 letters in `numpy.einsum`?

I does work, but django uses an iterator. It does not load all objects at once.

Django model queries by SQL keyword, For example, the SQL WHERE ID=1 statement in a Django query is written as . Item from django.db.models import Q # Get the Store records that don't have state 'CA' Django models support the creation of SQL queries with LIMIT and OFFSET caveat of only working on QuerySet 's that use the same model (e.g. Item ). The LIMIT and OFFSET doesn't work in the same way in Django, the way we expect it to work. For example. If we have to read next 10 rows starting from 10th row and if we specify : Author.objects.all()[10:10] It will return the empty record list. In order to fetch the next 10 rows, we have to add the offset to the limit. Author.objects.all()[10:10+10]

The LIMIT and OFFSET doesn't work in the same way in Django, the way we expect it to work.

For example.

If we have to read next 10 rows starting from 10th row and if we specify :

Author.objects.all()[10:10]

It will return the empty record list. In order to fetch the next 10 rows, we have to add the offset to the limit.

Author.objects.all()[10:10+10]

And it will return the record list of next 10 rows starting from the 10th row.

Pro Django, last executed query (cursor, sql, params)—Returns the last query that was issued to the by params, which will work correctly for all backends without any extra work. to indicate a limit of infinity, used when specifying an offset without a limit. I read here that Django querysets are lazy, it won't be evaluated until it is actually printed. I have made a simple pagination using the django's built-in pagination. I didn't realize there were apps already such as "django-pagination", and "django-endless" which does that job for.

Django RESTful Web Services: The easiest way to build Python , The easiest way to build Python RESTful APIs and web services with Django Gaston C. Hillar use the new settings we added to enable the customized offset​/limit pagination, The value specified for the limit query parameter is greater than 8, and The key advantage of working with generic views is that we can easily  In addition, only LIMIT, OFFSET, COUNT(*), ORDER BY, and specifying columns (i.e. slicing, count(), order_by(), and values() / values_list()) are allowed on the resulting QuerySet. Further, databases place restrictions on what operations are allowed in the combined queries. For example, most databases don’t allow LIMIT or OFFSET in the

Building RESTful Python Web Services, With the default setting, there is no limit and we will be able to process requests that specify a value for 1000000 for the limit query parameter. We definitely don't want our API to be able to generate a response with a million player scores or version of the limit/offset pagination style provided by Django REST Framework. argo. Pythonista, Djangonaut, NodeJSion, Angularian. 15 Specifying limit and offset in Django QuerySet wont work Dec 20 '18. 7 npm link a local module to a

Django, Django - limiting query results django query limit offset created some filters to apply on certain queries that happen in the specified api-endpoints. Looks like the solution in the question doesn't work with Django 1.7 anymore and raises  Performance issues paginating large QuerySet s. If you’re using a QuerySet with a very large number of items, requesting high page numbers might be slow on some databases, because the resulting LIMIT / OFFSET query needs to count the number of OFFSET records which takes longer as the page number gets higher.

Comments
  • That only means there are 21 entries in the database, Note that slice does not throw an error.
  • I never knew about that. I would upvote this 10 times if I could!
  • I think the slice should be [offset: offset + limit].
  • but thats not the case @neuront its [offset:limit]
  • Think of it as [FROM:TO]