In Django, why does an order list template printing order.customer.name run one query per order, and how does select_related() fix it?

## The setup: an order list page Picture a small shop app. An `Order` belongs to one `Customer` through a `ForeignKey`, and the order list page shows each order's date and customer name. ```python from django.db import models class Customer(models.Model): name = models.CharField(max_length=200) class Order(models.Model): customer = models.ForeignKey( Customer, on_delete=models.PROTECT, related_name='orders' ) placed_at = models.DateTimeField() ``` The view passes the 50 newest orders to a template that loops with `{% for order in orders %}` and prints `{{ order.customer.name }}` on every row. ## Why the loop runs one query per order A Django **model instance** holds the columns its query selected. For `Order`, that includes the foreign key column `customer_id`, but not the customer's own row. The related `Customer` object is loaded **on demand** the first time code reads the `order.customer` attribute. In Django 6.1 this on-demand behaviour is the default **fetch mode**, `FETCH_ONE`, which fetches the missing value for the one instance that was touched. So rendering the page goes like this: 1. The template's `for` loop evaluates the QuerySet: one `SELECT` on the order table. 2. Row 1 reads `order.customer.name`; no customer is cached on that instance, so Django runs a `SELECT` on the customer table filtered by that order's `customer_id`. 3. Row 2 does the same for its own customer, and so on for all 50 rows. 4. The page finishes after **1 + 50 queries**, even if only three distinct customers placed those orders. A few details matter in interviews: - Reading `order.customer_id` never queries: the key is a local column already on the order row. - Once `order.customer` has been loaded, it is cached on that instance, so a second read on the same row is free. - The template engine is not the culprit; the same loop in a view, a management command or a serializer produces the same queries. ## What select_related() changes `select_related('customer')` tells the ORM to **JOIN** the customer table into the list query and to build a `Customer` instance from the joined columns for every order row. The view becomes: ```python from django.shortcuts import render from .models import Order def order_list(request): orders = Order.objects.select_related('customer').order_by('-placed_at')[:50] return render(request, 'shop/order_list.html', {'orders': orders}) ``` Now the page runs **one query**. Each order arrives with its customer already cached, so `{{ order.customer.name }}` reads memory instead of the database. Django uses an `INNER JOIN` when the foreign key is non-nullable and a `LEFT OUTER JOIN` when it is `null=True`, so rows whose key is `NULL` are not dropped from the list. The method can sit anywhere in the chain: `Order.objects.filter(...).select_related('customer')` and `Order.objects.select_related('customer').filter(...)` are equivalent, because the QuerySet compiles one statement when it is evaluated. You can follow several hops with the double-underscore syntax, for example `select_related('customer__account_manager')`, and several relations at once with `select_related('customer', 'shipping_address')`. ## Which relations it can follow | Relation from `Order`'s point of view | Works with `select_related()`? | |---|---| | Forward `ForeignKey` (`order.customer`) | Yes | | Forward `OneToOneField` | Yes | | Reverse `OneToOneField`, named by its `related_name` | Yes | | Reverse `ForeignKey` (`order.items`) | No: raises `FieldError` | | `ManyToManyField` | No: raises `FieldError` | The rule is that `select_related()` handles **single-valued** links, where the join adds at most one related row per order. Many-valued links need `prefetch_related()`, which runs a separate query instead of a join. ## Fixes that do not fix it Interviewers often probe with tempting alternatives: - **Adding a database index** on `customer_id` makes each lookup faster but leaves all 51 round trips in place. - **Building a customer dictionary by hand** (collect the ids, query the customers once, look them up in the loop) does cut the count, but it re-implements what the ORM already offers and drifts out of date as the page grows. - **Caching the rendered page** hides the queries on a cache hit and brings every one of them back on a miss. The ORM-level fix is to load the related rows together with the orders, which is exactly what `select_related()` does. ## Habits worth keeping - **Name the relations.** Calling `select_related()` with no arguments follows every non-null foreign key it can reach; Django 6.1 deprecates that form and plans to remove it in 7.0. - **Join only what the page renders.** Every joined table widens every row, so a join nobody reads is pure cost. - **Clear it when reusing a QuerySet.** `select_related(None)` removes previously requested relations. - **Verify the count.** After the change, count the queries the page emits and confirm it dropped from 51 to 1. The interview-ready summary: the extra queries come from on-demand foreign-key access on each row, and `select_related()` removes them by moving the related row into the original query with a join.