Stage 5 · L22

Plan nested responses and avoid N+1 loading

Core · original Session 5

N+1 means one query loads N parent rows and then N more queries lazily load related rows as code iterates them. A tiny fixture can conceal the cost, and an async serializer may fail outright when it attempts unexpected I/O. This schema uses lazy='raise' so an absent load plan fails visibly.

For rental→equipment, joinedload is a good fit because each rental has one item and the join does not multiply rentals. The transfer task implements GET /rentals with joinedload and constructs RentalDetail inside the session. The baseline seed has both active and checked-in rows so it can distinguish filtering from merely returning the first fixture.

The test attaches SQLAlchemy's before_cursor_execute listener to the owned fixture engine, overrides the route session dependency to use that engine, and counts SELECT statements. A successful nested page must use one SELECT in this scoped fixture. Counting the unrelated app engine would produce a false zero. The override and listener are removed in finally.

One SELECT is not universally faster than two. selectinload is often useful for collections because it preserves the parent-page query and loads children in a bounded second query. Query count also does not certify a good index, small response or production latency. Inspect the actual rows, load shape and measured boundary together.

Select related rental rows; joinedload each rental's equipment; Build RentalDetail in owned session; One SELECT loads these rows

Follow the running code

Focused lesson example; see the end-of-stage capstone for the cumulative app · stage 05

statement = select(Rental).options(joinedload(Rental.equipment))
with SessionLocal() as session:
    rows = session.scalars(statement).all()
    names = [row.equipment.name for row in rows]
# names is plain data usable after close.

Predict and observe this focused example using the concepts explained above. Its boundary is stated in the focused answer.

Guided lab

Read the explanation and predict the focused example’s outcome.
Explain why the relationship must be loaded before closing the session. Compare joinedload for this many-to-one relation with a collection alternative.
Compare the observed outcome with the focused answer and state its boundary.

Expected: The chosen query loads the related equipment facts while the session is owned. Construct public nested DTOs there and return plain values after close. lazy='raise' catches accidental unloaded access. For collections, joined rows can duplicate parents and require unique(); selectinload uses a separate batched query. Those collection alternatives are comparisons here.

An unloaded relationship raises; observing a different engine would make query-count evidence invalid.

Focused exercise and answer

Complete this focused exercise before reading its answer. The full native transfer is introduced only at the end of the stage.

Your transfer task: Explain why the relationship must be loaded before closing the session. Compare joinedload for this many-to-one relation with a collection alternative.

Explain why the relationship must be loaded before closing the session. Compare joinedload for this many-to-one relation with a collection alternative.

Inspect the matching answer

This answer addresses the focused exercise above; the cumulative implementation is shown only after the stage prerequisites.

The chosen query loads the related equipment facts while the session is owned. Construct public nested DTOs there and return plain values after close. lazy='raise' catches accidental unloaded access. For collections, joined rows can duplicate parents and require unique(); selectinload uses a separate batched query. Those collection alternatives are comparisons here.

Check your reasoning

Would one SELECT always beat selectinload's two queries?

Show the explanation

No. Row multiplication, parent pagination and response size can favor a deliberate two-query collection load.

Reading progress

54 lessons remain open to guests. Marking a lesson read records reading only; it does not award assessment credit or a certificate.

Device reading marks require browser storage. Reading is always available.