Django Limit Offset

Summary: in this tutorial, you’ll learn how to limit a QuerySet in Django, which is equivalent to the SQL’s LIMIT and OFFSET clauses.

In practice, you rarely get all the rows from one or more tables in the database. Instead, you get a subset of rows for displaying on a web page.

Django uses the slicing syntax to limit a QuerySet to a specific number of objects. Behind the scenes, Django executes a SQL SELECT statement with LIMIT and OFFSET clauses.

Suppose you want to access the first 10 rows in a table, you can use the following slicing:

Entity.objects.all()[:10]Code language: Python (python)

Since the table stores the rows in an unspecified order, the “first 10 objects” become unpredictable.

Therefore, you should always sort the results before slicing the query. For example, the following gets the first 10 rows ordered by the field_name:

Entity.objects.all().order_by(field_name)[:10]Code language: Python (python)

To get the rows from 10 to 20, you can use the following slicing:

Entity.objects.all().order_by(field_name)[10:20]Code language: Python (python)

In general, the syntax for limiting results is as follows:

Entity.objects.all()[Offset:Offset+Limit]Code language: Python (python)

In this syntax, the Offset is the number of rows you want to skip and the Limit is the number of rows you want to retrieve.

Note that Django doesn’t support negative indexing like:

Entity.objects.all().order_by(field_name)[-10]Code language: Python (python)

Also, when you slice a QuerySet, Django returns a new QuerySet.

To retrieve the first or last row, you should use the first() or last() method because if the QuerySet is empty and you use a slice:

Entity.objects.all().order_by(field_name)[0]Code language: Python (python)

…then you’ll get an IndexError exception.

Django Limit Offset example

We’ll use the Employee model from the HR app for the demonstration. The Employee model maps to the hr_employee table:

The following example gets the first 10 employees ordered by their first names:

>>> Employee.objects.all().order_by('first_name')[:10] 
SELECT "hr_employee"."id",
       "hr_employee"."first_name",
       "hr_employee"."last_name",
       "hr_employee"."contact_id",
       "hr_employee"."department_id"
  FROM "hr_employee"
 ORDER BY "hr_employee"."first_name" ASC
 LIMIT 10
Execution time: 0.000000s [Database: default]
<QuerySet [<Employee: Aaron Pearson>, <Employee: Adam Crane>, <Employee: Adam Stewart>, <Employee: Adrienne Green>, <Employee: Alan Johnson>, <Employee: Alexa West>, <Employee: Alicia Wyatt>, <Employee: Amanda Benson>, <Employee: Amber Brown>, <Employee: Amy Lopez>]>Code language: Python (python)

The following example skips the first 10 rows and gets the next 10 rows from the hr_employee table:

>>> Employee.objects.order_by('first_name')[10:20] 
SELECT "hr_employee"."id",
       "hr_employee"."first_name",
       "hr_employee"."last_name",
       "hr_employee"."contact_id",
       "hr_employee"."department_id"
  FROM "hr_employee"
 ORDER BY "hr_employee"."first_name" ASC
 LIMIT 10
OFFSET 10
Execution time: 0.001001s [Database: default]
<QuerySet [<Employee: Amy Lee>, <Employee: Andre Perez>, <Employee: Andrea Mcintosh>, <Employee: Andrew Dixon>, <Employee: Andrew Guerra>, <Employee: Ann Chang>, 
<Employee: Anne Odom>, <Employee: Anthony Fuentes>, <Employee: Anthony Welch>, <Employee: Ashley Brown>]>Code language: Python (python)

Summary

Django uses array-slicing syntax to limit the number of objects returned by a QuerySet.

Did you find this tutorial helpful ?