skip to content

In Django's ORM, how does slicing a QuerySet become SQL, and which slices or indexes run a query immediately?

level: middleimportance: should knowfreq 45%

answer

  1. start and stop become SQL
  2. step forces a list
  3. an index means LIMIT 1
  4. no filtering after a slice
  5. negative values refused

basics

~20 s

On an unevaluated QuerySet, qs[a:b] becomes LIMIT and OFFSET and stays lazy. A step slice runs the query and returns a list; qs[n] runs LIMIT 1 at once, raising IndexError if empty. Negative indexes raise ValueError.

solid answer

~40 s

`Order.objects.order_by('-created_at')[10:20]` returns a new lazy QuerySet whose SQL gets `LIMIT 10 OFFSET 10` when it is evaluated. Adding a step, as in `[0:20:2]`, makes Django run the limited query immediately and return a Python list with the step applied. An integer index like `qs[3]` runs `LIMIT 1 OFFSET 3` right away and returns one instance, raising `IndexError` when there is no such row; `first()` is the variant that returns `None` instead. Negative indexes and bounds raise `ValueError`. After slicing, `filter()` or `order_by()` raise `TypeError`, so filter and order first. On an already evaluated QuerySet, slicing and indexing read from the result cache and return lists or instances without SQL.

code

python · 11 lines
python
recent = Order.objects.filter(status='open').order_by('-created_at')

page_2 = recent[10:20]          # lazy: LIMIT 10 OFFSET 10 when iterated
every_other = recent[:20:2]     # runs now, returns a list
newest = recent[0]              # runs now: LIMIT 1; IndexError if none
maybe = recent.first()          # runs now: LIMIT 1; None if none

# recent[:10].filter(total__gt=100)
#   TypeError: Cannot filter a query once a slice has been taken.
# recent[-1]
#   ValueError: Negative indexing is not supported.

go deeper

for a junior

Remember that qs[:10] adds LIMIT 10 and stays lazy, while qs[0] runs a query right away and raises IndexError when empty.

for a middle

Explain which slices evaluate (steps, integer indexes), why negative indexes and post-slice filters are refused, and that partial reads never fill the cache.

for a senior

Guard pagination: slice only ordered QuerySets, prefer first() where emptiness is normal, and watch OFFSET cost on deep pages.

for a principal

Decide how list endpoints page large tables so response time stays flat as data grows, rather than trusting OFFSET slicing everywhere.

## Slices become LIMIT and OFFSET A Django `QuerySet` supports Python's slice syntax, and on an **unevaluated** QuerySet the slice becomes part of the SQL instead of a Python operation: - `qs[:5]` becomes `LIMIT 5`. - `qs[5:10]` becomes `LIMIT 5 OFFSET 5`. - `qs[20:]` becomes an OFFSET with no upper bound; backends that require a LIMIT receive a very large one. The result is a **new, still lazy** QuerySet. Nothing runs until it is iterated, and only the requested rows are transferred. This is how paginating an unevaluated QuerySet avoids loading the whole table. ## What runs immediately | Expression | Evaluates now? | Returns | |---|---|---| | `qs[5:10]` | no | a lazy QuerySet | | `qs[0:10:2]` | yes | a list (step applied in Python) | | `qs[3]` | yes, `LIMIT 1 OFFSET 3` | one model instance, or `IndexError` | | `qs[-1]` | no, raises | `ValueError: Negative indexing is not supported.` | | any slice or index of an evaluated QuerySet | no SQL | from the result cache | SQL has no step clause, so Django fetches the limited rows and applies the step in Python. An integer index needs one row now, so Django clones the QuerySet, limits it to that position and runs it. These partial reads **do not fill** the original QuerySet's result cache; indexing the same position twice runs two queries. ## After a slice, the query is frozen Once a slice has been taken, Django refuses operations whose meaning would be ambiguous in SQL: 1. `filter()` or `exclude()` raise `TypeError: Cannot filter a query once a slice has been taken.` 2. `order_by()` raises `TypeError: Cannot reorder a query once a slice has been taken.` The correct order is filter, then order, then slice. If you genuinely need to filter the first N rows, express it as a subquery, for example `Order.objects.filter(pk__in=top_ids)` with `top_ids` being a sliced `values('pk')` QuerySet, where the backend supports a limit inside the subquery. ## Ordering makes slices meaningful - A slice of an **unordered** QuerySet returns whichever rows the database finds first; two page requests can overlap or skip rows. Always slice an explicitly ordered QuerySet, or a model with `Meta.ordering`. - `first()` and `last()` add ordering by primary key when the QuerySet has none, which makes them deterministic where `qs[0]` is not. - `last()` is not `qs[-1]`: it reverses the ordering and takes the first row, because negative indexing is not supported. ## Index access versus first() - `qs[0]` raises `IndexError` when there are no rows, and uses whatever order the QuerySet has. - `qs.first()` returns `None` when there are no rows, and orders by `pk` if unordered. - Both run one `LIMIT 1` query on an unevaluated QuerySet, and neither fills the cache. ## Slicing in Paginator and templates Django's own tools rely on lazy slicing: - `django.core.paginator.Paginator` calls the QuerySet's `count()` for the total and slices it with `object_list[bottom:top]` for each page, so an unevaluated QuerySet becomes one `COUNT(*)` plus one `LIMIT/OFFSET` query. - `Paginator` emits an `UnorderedObjectListWarning` when given an unordered QuerySet, for the overlap reason above. - The template `slice` filter applies Python slice syntax to its value, so `{{ orders|slice:":5" }}` on an unevaluated QuerySet is a limited query, while on an evaluated one it reads the cache. Passing an evaluated QuerySet or a list to either tool loses that advantage: every row has already been loaded. ## Practical notes - Large OFFSET values make the database walk and discard the skipped rows, so deep pages get slower; very deep pagination usually needs a different paging strategy. - `count()` on a sliced QuerySet counts at most the slice's rows, since Django counts over the limited query. - In templates, the `slice` filter applied to an unevaluated QuerySet also becomes a limited query, while slicing a list does not.

  • Why does Django refuse to filter a QuerySet after slicing it?
    A slice becomes `LIMIT/OFFSET`, which SQL applies after `WHERE`. Adding a filter afterwards would either change which rows the limit selects or need a subquery, and the intended meaning is unclear, so Django raises `TypeError` instead of guessing. Filter and order first, then slice; if you mean 'filter the top N', write the subquery explicitly.
  • When would qs[0] and qs.first() return different rows?
    When the QuerySet has no ordering and the model has no `Meta.ordering`: `first()` adds `ORDER BY pk`, while `qs[0]` takes whatever row the database returns first, which is not guaranteed. On an ordered QuerySet they return the same row, but `qs[0]` raises `IndexError` on an empty result where `first()` returns `None`.

saying these in an interview costs you the question

  • Slicing a QuerySet fetches all rows and slices in Python
  • qs[-1] returns the last row like a list
  • qs[0] returns None when the QuerySet is empty
  • You can filter a sliced QuerySet to narrow the page
  • Indexing a QuerySet twice reuses the first result