In Django, what does a Prefetch object with a custom queryset and to_attr give you that a plain prefetch_related() string cannot?

seniorshould knowfreq 52%

answer

shape the extra query
filter, order, join, slice
a list on a new attribute
same relation, prefetched twice

basics

~20 s

Prefetch('items', queryset=..., to_attr=...) lets you filter, order, slice or select_related the related rows in the extra query. to_attr stores the result as a plain list on a new attribute, so order.items.all() still means all items.

solid answer

~40 s

Prefetch(lookup, queryset=None, to_attr=None) is the object form of a prefetch lookup. The queryset argument shapes the extra query: filter() for a subset, order_by() for display order, select_related('product') to load each item's product in the same prefetch query, even a slice such as [:3] for the three latest items per order, which Django turns into a window function. to_attr stores the result as a plain list on a new attribute instead of in the manager's cache, so you can prefetch the same relation several ways and order.items.all() keeps meaning all items. Without to_attr, a filtered prefetch silently replaces what order.items.all() returns, which is why the docs recommend to_attr when filtering. The queryset cannot use values(), values_list() or raw(), and lookup order matters.

`Prefetch(lookup, queryset=None, to_attr=None)` is the object form of a prefetch lookup. The `queryset` argument shapes the extra query: `filter()` for a subset, `order_by()` for display order, `select_related('product')` to load each item's product in the same prefetch query, even a slice such as `[:3]` for the three latest items **per order**, which Django turns into a window function. `to_attr` stores the result as a plain `list` on a new attribute instead of in the manager's cache, so you can prefetch the same relation several ways and `order.items.all()` keeps meaning all items. Without `to_attr`, a filtered prefetch silently replaces what `order.items.all()` returns, which is why the docs recommend `to_attr` when filtering. The queryset cannot use `values()`, `values_list()` or `raw()`, and lookup order matters.

code

python · 13 lines

python

from django.db.models import Prefetch

from .models import Order, OrderItem

recent = OrderItem.objects.select_related('product').order_by('-id')[:3]

orders = Order.objects.select_related('customer').prefetch_related(
    Prefetch('items', queryset=OrderItem.objects.select_related('product')),
    Prefetch('items', queryset=recent, to_attr='recent_items'),
)

for order in orders:
    print(order.customer.name, len(order.items.all()), [i.product.name for i in order.recent_items])

go deeper

for a junior

Recall that Prefetch lets you give the extra query its own queryset and that to_attr puts the result on a new attribute as a list.

for a middle

Explain how a custom queryset filters, orders or joins the prefetched rows, and why to_attr keeps order.items.all() meaning all items.

for a senior

Use sliced prefetches for per-parent previews, collapse nested hops with select_related inside the queryset, and order lookups to avoid ValueError.

for a principal

Decide when shaped prefetches belong in a shared queryset method versus the view, so filtered relations never masquerade as complete ones.

Prefetch in one sentence

django.db.models.Prefetch(lookup, queryset=None, to_attr=None) is the object form of a prefetch_related() lookup. Prefetch('items') behaves exactly like the string 'items'; the two optional arguments are what make it worth using.

queryset supplies the base QuerySet for the extra query, so you control which related rows come back and how.
to_attr stores the result on a new attribute of each parent instead of in the related manager's cache.

What the queryset argument buys

The examples use Order, OrderItem (whose order key has related_name='items') and Product.

Filtering: OrderItem.objects.filter(status='shipped') prefetches only shipped items.
Ordering: OrderItem.objects.order_by('position') fixes the order the template shows.
Joins inside the prefetch: OrderItem.objects.select_related('product') loads each item's product in the same prefetch query, so items and products cost one query instead of the two that 'items__product' would run.
Per-parent limits: a sliced queryset such as OrderItem.objects.order_by('-id')[:3] returns at most three items per order. Django rewrites the slice into a ROW_NUMBER() window partitioned by the parent key, so it needs a backend that supports window functions and raises NotSupportedError otherwise.
Database choice: a queryset with .using('replica') sends that prefetch to another database alias.

What to_attr changes

	Without `to_attr`	With `to_attr='shipped_items'`
Where results live	the related manager's prefetch cache	a new attribute on each order
Type	a QuerySet with a filled result cache	a plain Python `list`
Effect on `order.items.all()`	returns the prefetched rows, filtered or not	untouched; still means all items
Same relation twice	not possible	yes, with different attribute names

The third row is the trap. A filtered Prefetch('items', queryset=OrderItem.objects.filter(status='shipped')) without to_attr makes order.items.all() return only shipped items on those instances, and any code that expected all items silently gets fewer. Django's documentation recommends to_attr whenever the prefetch queryset filters, for this reason. List storage is also cheaper: the documentation notes that storing a list can be significantly faster than building a cached QuerySet per parent.

The order list page, tuned

python

from django.db.models import Prefetch

recent = OrderItem.objects.select_related('product').order_by('-id')[:3]

orders = Order.objects.select_related('customer').prefetch_related(
    Prefetch('items', queryset=OrderItem.objects.select_related('product')),
    Prefetch('items', queryset=recent, to_attr='recent_items'),
)

This lists orders with their customer, every item with its product, and a separate three-item preview in order.recent_items, in three queries in total: orders joined to customers, all items joined to products, and the windowed preview query.

Nesting through a to_attr

A to_attr result can be traversed by later lookups, which lets a filtered subset carry its own nested prefetch:

python

Order.objects.prefetch_related(
    Prefetch(
        'items',
        queryset=OrderItem.objects.filter(status='shipped'),
        to_attr='shipped_items',
    ),
    'shipped_items__product',
)

Here the products of the shipped items load in one more query. The same result in fewer queries comes from select_related('product') inside the Prefetch queryset, which is usually the better choice for a single-valued hop; the string form earns its place when the next hop is itself many-valued.

Rules that raise errors

The prefetch queryset must return model instances: values(), values_list() or raw() there raises ValueError.
to_attr must not reuse the name of a field on the model, or ValueError is raised.
Lookup order matters. prefetch_related('items__product', Prefetch('items', queryset=...)) raises ValueError, because 'items' was already traversed with an implicit queryset when the first lookup ran; put the Prefetch first.
A lookup that walks through a to_attr, such as 'recent_items__product', must come after the Prefetch that defines recent_items, or AttributeError is raised.

When not to reach for it

If the page needs every related row unfiltered and nothing nested, the plain string is clearer.
If only an aggregate per parent is needed, loading rows at all is the wrong tool.
to_attr results are plain lists, not managers or QuerySets, so order.recent_items.filter(...) fails with AttributeError; filter them in Python.
A prefetched list is a snapshot of the moment the QuerySet was evaluated; it does not follow later writes.

In an interview, the crisp answer is: Prefetch lets you shape the extra query, and to_attr keeps the shaped result from masquerading as the full relation.

## Prefetch in one sentence `django.db.models.Prefetch(lookup, queryset=None, to_attr=None)` is the object form of a `prefetch_related()` lookup. `Prefetch('items')` behaves exactly like the string `'items'`; the two optional arguments are what make it worth using. - `queryset` supplies the base QuerySet for the extra query, so you control which related rows come back and how. - `to_attr` stores the result on a new attribute of each parent instead of in the related manager's cache. ## What the queryset argument buys The examples use `Order`, `OrderItem` (whose `order` key has `related_name='items'`) and `Product`. - **Filtering:** `OrderItem.objects.filter(status='shipped')` prefetches only shipped items. - **Ordering:** `OrderItem.objects.order_by('position')` fixes the order the template shows. - **Joins inside the prefetch:** `OrderItem.objects.select_related('product')` loads each item's product in the same prefetch query, so items and products cost one query instead of the two that `'items__product'` would run. - **Per-parent limits:** a sliced queryset such as `OrderItem.objects.order_by('-id')[:3]` returns at most three items **per order**. Django rewrites the slice into a `ROW_NUMBER()` window partitioned by the parent key, so it needs a backend that supports window functions and raises `NotSupportedError` otherwise. - **Database choice:** a queryset with `.using('replica')` sends that prefetch to another database alias. ## What to_attr changes | | Without `to_attr` | With `to_attr='shipped_items'` | |---|---|---| | Where results live | the related manager's prefetch cache | a new attribute on each order | | Type | a QuerySet with a filled result cache | a plain Python `list` | | Effect on `order.items.all()` | returns the prefetched rows, **filtered or not** | untouched; still means all items | | Same relation twice | not possible | yes, with different attribute names | The third row is the trap. A filtered `Prefetch('items', queryset=OrderItem.objects.filter(status='shipped'))` **without** `to_attr` makes `order.items.all()` return only shipped items on those instances, and any code that expected all items silently gets fewer. Django's documentation recommends `to_attr` whenever the prefetch queryset filters, for this reason. List storage is also cheaper: the documentation notes that storing a list can be significantly faster than building a cached QuerySet per parent. ## The order list page, tuned ```python from django.db.models import Prefetch recent = OrderItem.objects.select_related('product').order_by('-id')[:3] orders = Order.objects.select_related('customer').prefetch_related( Prefetch('items', queryset=OrderItem.objects.select_related('product')), Prefetch('items', queryset=recent, to_attr='recent_items'), ) ``` This lists orders with their customer, every item with its product, and a separate three-item preview in `order.recent_items`, in three queries in total: orders joined to customers, all items joined to products, and the windowed preview query. ## Nesting through a to_attr A `to_attr` result can be traversed by later lookups, which lets a filtered subset carry its own nested prefetch: ```python Order.objects.prefetch_related( Prefetch( 'items', queryset=OrderItem.objects.filter(status='shipped'), to_attr='shipped_items', ), 'shipped_items__product', ) ``` Here the products of the shipped items load in one more query. The same result in fewer queries comes from `select_related('product')` inside the `Prefetch` queryset, which is usually the better choice for a single-valued hop; the string form earns its place when the next hop is itself many-valued. ## Rules that raise errors 1. The prefetch queryset must return model instances: `values()`, `values_list()` or `raw()` there raises `ValueError`. 2. `to_attr` must not reuse the name of a field on the model, or `ValueError` is raised. 3. **Lookup order matters.** `prefetch_related('items__product', Prefetch('items', queryset=...))` raises `ValueError`, because `'items'` was already traversed with an implicit queryset when the first lookup ran; put the `Prefetch` first. 4. A lookup that walks through a `to_attr`, such as `'recent_items__product'`, must come after the `Prefetch` that defines `recent_items`, or `AttributeError` is raised. ## When not to reach for it - If the page needs every related row unfiltered and nothing nested, the plain string is clearer. - If only an aggregate per parent is needed, loading rows at all is the wrong tool. - `to_attr` results are plain lists, not managers or QuerySets, so `order.recent_items.filter(...)` fails with `AttributeError`; filter them in Python. - A prefetched list is a snapshot of the moment the QuerySet was evaluated; it does not follow later writes. In an interview, the crisp answer is: `Prefetch` lets you shape the extra query, and `to_attr` keeps the shaped result from masquerading as the full relation.

How would you show only the three most recent items for each order?
Prefetch a sliced, ordered queryset into its own attribute: `Prefetch('items', queryset=OrderItem.objects.order_by('-id')[:3], to_attr='recent_items')`. Django applies the limit per order with a `ROW_NUMBER()` window partitioned by the order key. On a backend without window function support it raises `NotSupportedError`.
What goes wrong with prefetch_related('items__product', Prefetch('items', queryset=...))?
It raises `ValueError` saying the `'items'` lookup was already seen with a different queryset. The string lookup traversed `items` first with an implicit default queryset, so the later `Prefetch` would redefine it. Put the `Prefetch` object first and the deeper string after it.

saying these in an interview costs you the question

to_attr stores a QuerySet you can keep filtering without new queries
A filtered Prefetch without to_attr leaves order.items.all() returning every item
A Prefetch queryset can use values() to fetch only a few columns
Slicing a Prefetch queryset caps the total number of items across all orders
The order of lookups passed to prefetch_related() never matters

In Django, what does a Prefetch object with a custom queryset and to_attr give you that a plain prefetch_related() string cannot?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

Prefetch in one sentence

What the queryset argument buys

What to_attr changes

The order list page, tuned

Nesting through a to_attr

Rules that raise errors

When not to reach for it

▾common follow-ups2 questions

▾red flags5 flags

Prefetch in one sentence

What the queryset argument buys

What to_attr changes

The order list page, tuned

Nesting through a to_attr

Rules that raise errors

When not to reach for it

answer

go deeper