beginnerintermediateadvanced

Django · Section 25

Query Projection

Level: advanced
Read: 18 min
Concepts: 2

values()/values_list() project a QuerySet down to dicts or tuples — real SQL-level projections that skip model-instance construction entirely, at the cost of losing model methods/properties. only()/defer() keep real model instances while limiting which fields are loaded immediately, but touching a deferred field afterward silently triggers its own separate query — the same shape as a property-driven N+1, just from Django's own deferred-loading machinery. only() replaces its field set on each call while defer() accumulates, and combining only() with select_related() has a documented, error-raising requirement: the joined relation's own needed field(s) must be included.

Django overview

Python Django Full Course for Beginners | Complete All-in-One Tutorial | 3 HoursDave Gray

What is true here

values()/values_list() return dicts/tuples, not model instances — lighter, directly usable for JSON/CSV/simple lookups, with no model methods/properties and no risk of a surprise deferred-field query.
flat=True (single field only) unwraps 1-tuples into bare values; named=True returns attribute-accessible namedtuples.
only()/defer() keep real model instances but limit the immediately-loaded field set — accessing a deferred field afterward triggers a separate query per field, invisible from the calling code.
only() REPLACES its field set on each call; defer() ACCUMULATES across calls — a real, easy-to-miss asymmetry between the two.
only() combined with select_related() must include the joined relation's own needed field(s) — omitting it is a documented error, not silently ignored.

What you will be able to do

Choose values()/values_list() when model instance behavior is never needed
Use only()/defer() correctly, without triggering a hidden deferred-field N+1
Combine only() with select_related() correctly, avoiding the documented error case

values() and values_list()

Real SQL-level projections to dicts or tuples — no model instance built at all.

values() and values_list()

coreintermediate

values("a", "b") returns dicts ({"a": ..., "b": ...}) instead of model instances — no model methods/properties available, but lighter and directly JSON-serializable. values_list("a", "b") returns tuples instead — flat=True (single field only) unwraps 1-tuples into plain values, and named=True returns namedtuples with attribute access. Both are real projections: only the named fields are actually selected in the SQL, not just hidden after the fact.

Think of it as

A normal QuerySet gives back full model instances — every field loaded, every method/property available, at the cost of building real Python objects. values()/values_list() are for the moment none of that object machinery is needed — just the raw data, shaped as a dict or a tuple, which is both cheaper to build and often exactly the shape something else (a JSON API response, a CSV export, a simple lookup) already wants. The choice between values() and values_list() is really about downstream ergonomics: dicts read naturally by key, tuples are more compact and fine when field ORDER is enough context.

python

Model.objects.values("a", "b")                    # dicts
Model.objects.values_list("a", flat=True)          # a flat list, single field
Model.objects.values_list("a", "b", named=True)     # namedtuples

What we're doing: Get a flat list of order IDs (for use elsewhere, e.g. a second query's __in filter) without building full Order instances.

orders/services.pypython

pending_ids = Order.objects.filter(status="PENDING").values_list("id", flat=True)
LineItem.objects.filter(order_id__in=pending_ids)

1: flat=True with a single field ("id") returns bare ids directly — [1, 5, 12, ...] — rather than [(1,), (5,), (12,)], which is both easier to read and exactly what __in expects on the next line.

Why this works: Building full Order objects here would be wasted work — nothing about status, total, or any other field is needed, only the ids, to feed a second query's __in= lookup. values_list(flat=True) gets exactly that shape directly from the database, without the overhead of instantiating a full model object per row.

Using flat=True with more than one field

Wrong

python

Order.objects.values_list("id", "total", flat=True)   # TypeError

Better

python

Order.objects.values_list("id", "total")   # tuples: (id, total)
# or, if only ids are needed:
Order.objects.values_list("id", flat=True)

What you see: TypeError: 'flat' is not valid when values_list is called with more than one field.

Why: flat=True exists specifically to unwrap a SINGLE-field tuple into a bare value — with two or more fields, there is no single value to unwrap into (each row would still need to be a pair), so Django rejects the combination outright rather than silently picking one field to flatten.

values() vs values_list()

values("a", "b")

+Returns dicts — {"a": ..., "b": ...}
+Directly JSON-serializable
+Read naturally by key

values_list("a", "b")

−Returns tuples — (a, b)
−flat=True unwraps a single field to bare values
−named=True gives namedtuples, dot-access

values() vs values_list() variants

values() vs values_list() variants
Call	Returns
values("a", "b")	[{"a": ..., "b": ...}, ...] — dicts
values_list("a", "b")	[(a, b), ...] — tuples
values_list("a", flat=True)	[a, a, ...] — bare values, single field only
values_list("a", "b", named=True)	[Row(a=..., b=...), ...] — namedtuples

Together

python

Order.objects.values("id", "total")
# [{'id': 1, 'total': Decimal('50.00')}, ...]

Order.objects.values_list("id", flat=True)
# [1, 2, 3, ...] — a flat list of ids, not [(1,), (2,), (3,)]

Remember: values() returns dicts; values_list() returns tuples (flat=True for a single field's bare values; named=True for attribute-accessible namedtuples). Both are real SQL projections — only the requested columns are ever selected — and both skip model-instance construction, the right choice whenever full model objects (with their methods/properties) are not actually needed.

only() and defer()

Still real model instances, with a lazy-loading trapdoor and a documented select_related() interaction.

only() and defer()

coreadvanced

only("a", "b") loads ONLY those fields immediately (every other field becomes deferred); defer("a", "b") loads everything EXCEPT those fields immediately — opposite framings of the same mechanism. Unlike values()/values_list(), the result is still a REAL model instance — accessing a deferred field afterward silently triggers a SEPARATE query to fetch just that field, one extra query per deferred field actually touched. Combining only()/defer() with select_related() has a real documented gotcha: omitting a select_related()-joined relation's own field from only() is an error.

Think of it as

only()/defer() keep the object a REAL model instance — the trade-off values()/values_list() make (no model methods/properties, but no risk of a surprise query) is exactly reversed here: you keep full model behavior, but touching a deferred field is a trapdoor — it looks like reading an already-loaded attribute, and is actually a fresh query, exactly the property-driven N+1 pattern from earlier in this topic, just triggered by Django's own deferred-field machinery instead of a custom @property. only() and defer() are two ways to describe the SAME resulting field set from opposite directions — only() says 'load just these,' defer() says 'skip just these' — and the last one called generally wins when they're combined, per Django's own documented resolution rules.

python

Model.objects.only("a", "b")     # load only these immediately
Model.objects.defer("a", "b")    # load everything except these immediately

What we're doing: Load only the fields a list view actually displays, correctly including a select_related()-joined relation's field in the only() list.

catalog/views.pypython

books = Book.objects.select_related("author").only(
    "title", "published_date", "author__name",
)
# author__name is included — required, since author was select_related()-joined

1: select_related("author") JOINs the author table into this same query.
2: author__name is listed alongside title/published_date — omitting it, while still select_related()-ing author, is exactly the documented error case: the joined relation's own needed field(s) must appear in only() too.

Why this works: select_related() and only() interact at the SQL level — select_related() adds the JOIN, only() controls which columns of the WHOLE result (base table AND joined tables) are actually selected; leaving out a select_related()-joined field from only() creates an inconsistency Django flags as an error, rather than silently guessing which columns were meant to survive the JOIN.

Omitting a select_related()-joined relation's field from only(), hitting the documented error

Wrong

python

Book.objects.select_related("author").only("title")
# author was select_related()-joined but its own fields are missing from only()

Better

python

Book.objects.select_related("author").only("title", "author__name")

What you see: Django raises an error (FieldError, depending on version) at query-build or evaluation time — the exact message varies, but the underlying cause is the same: a select_related()-joined relation whose own fields were never named in only().

Why: select_related("author") tells Django to JOIN the author table in; only("title") then tells Django to select ONLY the title column — but never says what to do about the already-JOINed author columns, an inconsistency Django surfaces as an error rather than silently guessing (either dropping the JOIN's data or including every author column despite only() suggesting otherwise).

only() vs defer() — opposite framings, same mechanism

only("headline")

+Loads ONLY headline (+ pk) immediately
+Every other field deferred
+Repeated only() calls REPLACE, not accumulate

defer("body")

−Loads everything EXCEPT body immediately
−body loaded on first access — a separate query
−Repeated defer() calls DO accumulate

only() vs defer() — opposite framings, same mechanism

only() vs defer() — opposite framings, same mechanism
Call	Loaded immediately	Deferred
only("headline")	headline (+ pk, always)	every other field
defer("body")	every field except body	body
only("a", "b").only("c")	c only — only() REPLACES, does not accumulate	a, b, and everything else
defer("a").defer("b")	everything except a and b	a and b — defer() DOES accumulate

Together

python

entry = Entry.objects.defer("body").get(pk=1)   # 1 query, body NOT loaded
print(entry.headline)                             # already loaded — no extra query
print(entry.body)                                 # DEFERRED — triggers a SECOND query, just for body

Remember: only()/defer() keep real model instances (unlike values()/values_list()) — but touching a deferred field afterward triggers a separate query, invisible from the calling code, the same shape as a property-driven N+1. only() with select_related() must include the joined relation's own needed field(s), or Django raises an error. Never defer a field that will actually be read inside a loop.