Django / Reliable application workflows / Lesson 7

ORM queries and relationship loading

Build bounded queries, understand lazy evaluation, and choose related-object loading from the actual response contract.

About 4 min readContent reviewed 2026-10-25

On this page

Lesson goals

A QuerySet describes work before evaluation
Load a single-valued relation with select_related
Keep query correctness separate from convenience

A QuerySet describes work before evaluation

A QuerySet is a composable database query description. Chaining filter, exclude, order_by, and selected projections normally constructs another query description rather than immediately fetching rows. Evaluation occurs through operations such as iteration or materialization, and a particular evaluated queryset can cache results under its documented behavior. Rebuilding an equivalent queryset is not the same cache instance. Decide whether you need objects, scalar values, existence, or a count before loading every row. An exists call followed by full iteration may perform more total work than one deliberate evaluation when the objects were needed anyway.

Relations carry cardinality and deletion policy

A ForeignKey points from many records to one related object, while many-to-many and reverse collections have different loading costs. Define whether a relation is optional and what happens when its target is deleted. on_delete expresses Django’s deletion behavior, not a substitute for reviewing database and domain constraints. Use settings.AUTH_USER_MODEL when declaring a relation to the configured user model rather than freezing an assumed user class. The local teaching relation uses a simple Instructor model to isolate ORM behavior without installing the authentication subsystem. It does not imply that a real instructor identity or access rule should be implemented by that tiny fixture.

Load a single-valued relation with select_related

The first example creates its two tables in in-memory SQLite, inserts one instructor and two courses, and reads the instructor names using select_related. For this simple foreign key, the related data is joined into the query so reading each instructor does not require a separate lookup. Deterministic course ordering makes the printed result stable. The example does not claim a universal query count under arbitrary custom managers or additional work. Keep selected primary and foreign-key columns when narrowing projections, and inspect real queries if a response accesses relationships through templates or serializers. A small output can still hide expensive repeated reads.

python Isolated executable example

from django.conf import settings
settings.configure(
    SECRET_KEY="synthetic-course-key",
    USE_I18N=False, USE_TZ=True,
    INSTALLED_APPS=[],
    DATABASES={"default": {"ENGINE": "django.db.backends.sqlite3", "NAME": ":memory:"}},
    DEFAULT_AUTO_FIELD="django.db.models.AutoField",
    ALLOWED_HOSTS=["testserver"],
)
import django
django.setup()

from django.db import connection, models

class Instructor(models.Model):
    name = models.CharField(max_length=60)
    class Meta:
        app_label = "lab"

class Course(models.Model):
    title = models.CharField(max_length=100)
    instructor = models.ForeignKey(Instructor, on_delete=models.PROTECT)
    class Meta:
        app_label = "lab"

with connection.schema_editor() as editor:
    editor.create_model(Instructor)
    editor.create_model(Course)
mira = Instructor.objects.create(name="Mira")
Course.objects.create(title="Django", instructor=mira)
Course.objects.create(title="Python", instructor=mira)
rows = Course.objects.select_related("instructor").order_by("title")
print([(row.title, row.instructor.name) for row in rows])

Expected output

[('Django', 'Mira'), ('Python', 'Mira')]

The isolated foreign-key fixture uses a joined single-valued relation. It creates no configured application tables.

Filter and project before materializing

The second example uses a separate simple fixture to select only published titles in a stable order. Filtering the Python list after fetching all records could yield the same visible titles while transferring and retaining unnecessary rows. Apply tenant, ownership, or publication visibility at the query boundary under the endpoint’s contract, then authorize operations as needed. select_related suits single-valued joins, while prefetch_related performs separate fetches and associates results for collections and other supported relationships. Prefetching everything can exhaust memory, so bound the root set and request only the relationship graph the response needs.

python Isolated executable example

from django.conf import settings
settings.configure(
    SECRET_KEY="synthetic-course-key",
    USE_I18N=False, USE_TZ=True,
    INSTALLED_APPS=[],
    DATABASES={"default": {"ENGINE": "django.db.backends.sqlite3", "NAME": ":memory:"}},
    DEFAULT_AUTO_FIELD="django.db.models.AutoField",
    ALLOWED_HOSTS=["testserver"],
)
import django
django.setup()

from django.db import connection, models

class Course(models.Model):
    title = models.CharField(max_length=100)
    published = models.BooleanField(default=False)
    class Meta:
        app_label = "lab"

with connection.schema_editor() as editor:
    editor.create_model(Course)
Course.objects.create(title="Django", published=True)
Course.objects.create(title="Draft", published=False)
print(list(Course.objects.filter(published=True).order_by("title", "pk").values_list("title", flat=True)))

Expected output

['Django']

The database filter excludes Draft before the result becomes a Python list. The projection returns only approved titles.

Keep query correctness separate from convenience

Use parameterized ORM lookups for values and allowlist structural choices such as ordering fields. Group Q expressions intentionally so an OR branch cannot escape a required access scope. Joins can duplicate root rows or multiply aggregates, so state the reporting grain before adding distinct or counts. Test empty sets, missing optional relations, duplicate related rows, and multiple root objects. Inspect query evidence with synthetic fixtures rather than relying on one fast response. The ORM reduces repetitive SQL construction, but it does not guarantee a correct business scope, safe raw SQL, or an efficient plan for every data distribution and database engine.

Common errors and fixes

A template causes one instructor query per row

Why it happens: A lazy foreign key is accessed repeatedly.
How to fix it: Load the required relation through select_related where suitable.
Prevent it next time: Test multiple-row rendering with query evidence.

A scope is bypassed by an OR condition

Why it happens: Boolean grouping does not preserve the required constraint.
How to fix it: Group alternatives beneath the invariant scope.
Prevent it next time: Include excluded-user or tenant rows in tests.

Prefetching increases memory unexpectedly

Why it happens: The root set and related collections are unbounded.
How to fix it: Limit roots and load only required relations.
Prevent it next time: Measure retained rows as well as query count.

Check your understanding

1. A list needs each instructor’s name but only a count of registrations. What shape should be considered?

Show worked solution

Use a suitable single-valued relationship load for the instructor and an aggregate count for registrations instead of fetching every registration object. Keep visibility scope and root bounds explicit, then inspect the generated query and verify counts under duplicate or multi-join cases.

2. Why can an ORM query be parameterized yet still expose another user’s data?