← Django: practical web applications Progress & review ↗

Typorithm · easy

Related objects without hidden N+1 queries

pythondjangoindependent-practice

Saved on this device

Full lesson · 4 learning steps Open any exercise for focused practice

Step 1 of 4 · intro card

Related objects without hidden N+1 queries

Concept and contract

Choose a loading strategy from relationship cardinality and measure the queries that rendering actually performs.

A list of roles can be fetched in one query and still trigger another query for every related company accessed during rendering. That is the N+1 pattern.

Django offers different loading strategies for single-valued and multi-valued relationships. This lesson uses both a foreign key from role to company and the reverse company-to-roles collection, including one empty company so the displayed population stays explicit.

Mechanism and worked example

select_related joins the single company for each selected role, allowing the forward rendering pass to use one query in this fixture.

select_related joins the single company for each selected role, allowing the forward rendering pass to use one query in this fixture. prefetch_related separately fetches companies and their role collections, then associates them in Python.

The reverse pass therefore uses two queries here. The returned groups use company identifiers rather than assuming that a repeated display name denotes the same company entity.

Complete worked example

python

from bootstrap_lab import django_setup
django_setup(__name__)

from bootstrap_lab import install_models
from django.db import models, connection
from django.test.utils import CaptureQueriesContext

class Company(models.Model):
    name = models.CharField(max_length=40)
    class Meta:
        app_label = "framework_lab"

class Role(models.Model):
    company = models.ForeignKey(Company, on_delete=models.CASCADE, related_name="roles")
    class Meta:
        app_label = "framework_lab"

install_models(Company, Role)

def relation_counts(names):
    Role.objects.all().delete()
    Company.objects.all().delete()
    for index, name in enumerate(names, 1):
        company = Company.objects.create(id=index, name=name)
        Role.objects.create(id=index, company=company)
    Company.objects.create(id=len(names) + 1, name="Empty")
    with CaptureQueriesContext(connection) as forward:
        selected = [role.company.name for role in Role.objects.select_related("company").order_by("id")]
    with CaptureQueriesContext(connection) as reverse:
        groups = [[company.id, len(company.roles.all())] for company in Company.objects.prefetch_related("roles").order_by("id")]
    return {"names": selected, "groups": groups, "forward_queries": len(forward), "reverse_queries": len(reverse)}

Reasoning: select_related joins the single company for each selected role, allowing the forward rendering pass to use one query in this fixture. prefetch_related separately fetches companies and their role collections, then associates them in Python.

Counterexamples and common mistakes

The counterexample removes select_related while leaving the forward Python loop unchanged.

The counterexample removes select_related while leaving the forward Python loop unchanged. The rendered names still look correct, but the measured query count grows with the number of roles.

Prefetching everything is not a universal fix: it can retain large collections in memory and does not cover every later differently filtered manager call. Measure the access pattern actually used by the view instead of asserting performance from method names alone.

Counterexample: a runnable framework mistake

python

from bootstrap_lab import django_setup
django_setup(__name__)

from bootstrap_lab import install_models
from django.db import models, connection
from django.test.utils import CaptureQueriesContext

class Company(models.Model):
    name = models.CharField(max_length=40)
    class Meta:
        app_label = "framework_lab"

class Role(models.Model):
    company = models.ForeignKey(Company, on_delete=models.CASCADE, related_name="roles")
    class Meta:
        app_label = "framework_lab"

install_models(Company, Role)

def relation_counts(names):
    Role.objects.all().delete()
    Company.objects.all().delete()
    for index, name in enumerate(names, 1):
        company = Company.objects.create(id=index, name=name)
        Role.objects.create(id=index, company=company)
    Company.objects.create(id=len(names) + 1, name="Empty")
    with CaptureQueriesContext(connection) as forward:
        selected = [role.company.name for role in Role.objects.order_by("id")]
    with CaptureQueriesContext(connection) as reverse:
        groups = [[company.id, len(company.roles.all())] for company in Company.objects.prefetch_related("roles").order_by("id")]
    return {"names": selected, "groups": groups, "forward_queries": len(forward), "reverse_queries": len(reverse)}

Reasoning: The counterexample removes select_related while leaving the forward Python loop unchanged. The rendered names still look correct, but the measured query count grows with the number of roles.

Independent practice: one bounded framework change

Hide zero-role groups from the returned presentation while keeping the underlying fixture and loading strategy unchanged.

Hide zero-role groups from the returned presentation while keeping the underlying fixture and loading strategy unchanged. Begin with two hiring companies and the empty company, then a completely empty hiring population.

Filter the already loaded group list rather than adding an accidental per-company database query. The task changes the displayed population without changing the meaning of the measured forward and reverse query counts.

Step 1: Remove zero-role groups from the returned presentation. Step 2: Do not add queries while filtering already loaded group counts.

Practice starter: solve before reading the solution

python

from bootstrap_lab import django_setup
django_setup(__name__)

from bootstrap_lab import install_models
from django.db import models, connection
from django.test.utils import CaptureQueriesContext

class Company(models.Model):
    name = models.CharField(max_length=40)
    class Meta:
        app_label = "framework_lab"

class Role(models.Model):
    company = models.ForeignKey(Company, on_delete=models.CASCADE, related_name="roles")
    class Meta:
        app_label = "framework_lab"

install_models(Company, Role)

def relation_counts(names):
    # Remove this placeholder, then adapt the retained baseline:
    # Remove zero-role groups from the returned presentation.
    raise NotImplementedError
    Role.objects.all().delete()
    Company.objects.all().delete()
    for index, name in enumerate(names, 1):
        company = Company.objects.create(id=index, name=name)
        Role.objects.create(id=index, company=company)
    Company.objects.create(id=len(names) + 1, name="Empty")
    with CaptureQueriesContext(connection) as forward:
        selected = [role.company.name for role in Role.objects.select_related("company").order_by("id")]
    with CaptureQueriesContext(connection) as reverse:
        groups = [[company.id, len(company.roles.all())] for company in Company.objects.prefetch_related("roles").order_by("id")]
    return {"names": selected, "groups": groups, "forward_queries": len(forward), "reverse_queries": len(reverse)}

Reasoning: Remove zero-role groups from the returned presentation. Do not add queries while filtering already loaded group counts.

Hint 1: Filter the completed groups list using its count field.
Hint 2: Keep distinct company identifiers even when display names repeat.

Solution, tests and trade-offs

The cases inspect both returned relationships and the actual SQL counts.

The cases inspect both returned relationships and the actual SQL counts. A duplicate company display name remains two separate identifiers, while an empty company still appears in the worked view.

Practice removes only zero-count groups. The query budgets are tied to these controlled shapes and accesses; they do not prove constant memory use or that an arbitrarily deep graph can be fetched with the same two-query plan.

Practice solution and acceptance checks

python

from bootstrap_lab import django_setup
django_setup(__name__)

from bootstrap_lab import install_models
from django.db import models, connection
from django.test.utils import CaptureQueriesContext

class Company(models.Model):
    name = models.CharField(max_length=40)
    class Meta:
        app_label = "framework_lab"

class Role(models.Model):
    company = models.ForeignKey(Company, on_delete=models.CASCADE, related_name="roles")
    class Meta:
        app_label = "framework_lab"

install_models(Company, Role)

def relation_counts(names):
    Role.objects.all().delete()
    Company.objects.all().delete()
    for index, name in enumerate(names, 1):
        company = Company.objects.create(id=index, name=name)
        Role.objects.create(id=index, company=company)
    Company.objects.create(id=len(names) + 1, name="Empty")
    with CaptureQueriesContext(connection) as forward:
        selected = [role.company.name for role in Role.objects.select_related("company").order_by("id")]
    with CaptureQueriesContext(connection) as reverse:
        groups = [[company.id, len(company.roles.all())] for company in Company.objects.prefetch_related("roles").order_by("id")]
    groups = [group for group in groups if group[1] > 0]
    return {"names": selected, "groups": groups, "forward_queries": len(forward), "reverse_queries": len(reverse)}

Reasoning: The cases inspect both returned relationships and the actual SQL counts. A duplicate company display name remains two separate identifiers, while an empty company still appears in the worked view.

Interview reasoning and next step

Explain why select_related and prefetch_related solve different cardinality problems.

Explain why select_related and prefetch_related solve different cardinality problems. Then identify which extra manager operation would bypass a prefetched collection and how you would detect it with a regression test.

A production response should also bound parent and child populations. The next lesson turns from read efficiency to write correctness, where a fast response is still wrong if a partially completed transaction is advertised as a successful operation.

Optional stretch — after the core task works: Bound parent and child collections and measure a differently filtered related manager access.

Computer practice: https://applydjinn.com/typorithm/python-frameworks-lab-20261105.zip. Install the pinned dependencies in an isolated environment, run verify.py --track django, then edit the retained exercise baseline and check it with --practice dj-relations. The lab uses owned in-memory databases, local framework clients and tiny CPU tensors. It never reads the working ApplyDjinn database, contacts production feeds or downloads models.

Choose a loading strategy from relationship cardinality and measure the queries that rendering actually performs.