Stage 5 · L23

Filter, order and paginate at the correct boundary

Core · original Session 5

A collection contract needs explicit filtering, ordering and bounds. GET /rentals accepts status active or checked_in, limit 1–100 and offset 0–10000. The SQL WHERE filter is applied before ORDER BY, LIMIT and OFFSET. Filtering a returned page in Python would hide matching records beyond that page and produce misleading empty results.

Order by created_at and then id so equal timestamps have a deterministic tie-breaker. A timestamp alone does not define a total order. This checkpoint uses ascending bounded offset pagination for clarity. It does not claim a cursor contract. Concurrent inserts/deletes can still shift page membership between offset requests.

Later actor scoping must join the same query before pagination. Fetching an unrestricted page and discarding other users' rows afterward can leak counts, omit the caller's own records and waste resources. Stage 07 adds owner/role filters to this base query. Query limit validation does not replace that authorization rule.

The independent transfer checks a filtered active page with limit1, nested equipment and one SELECT, then invalid status/limit422. It proves this query's contract for fixture data. A production query plan, suitable indexes, cursor encoding and performance under large data require separate measurement; they are not certified by two seeded rentals.

Select the requested rows; Status WHERE predicate; ORDER BY created_at, id; Bounded LIMIT and OFFSET; Build the allowed response

Follow the running code

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

SELECT id, status, created_at FROM rentals
WHERE status = 'active'
ORDER BY created_at, id
LIMIT 1 OFFSET 0;

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.
An older checked_in row precedes a newer active row. Which row belongs in the active limit1 page, and why must the timestamp order be deterministic?
Compare the observed outcome with the focused answer and state its boundary.

Expected: The newer active row belongs in the page because filtering occurs before the order and slice. Slicing the global rows first can select the excluded checked_in row and then return nothing. Distinct explicit timestamps make this counterexample reliable; id breaks ties. Concurrent changes can still shift offset pages.

Filtering the requested status after LIMIT can omit qualifying rows; stable ordering does not freeze concurrently changing data.

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: An older checked_in row precedes a newer active row. Which row belongs in the active limit1 page, and why must the timestamp order be deterministic?

An older checked_in row precedes a newer active row. Which row belongs in the active limit1 page, and why must the timestamp order be deterministic?

Inspect the matching answer

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

The newer active row belongs in the page because filtering occurs before the order and slice. Slicing the global rows first can select the excluded checked_in row and then return nothing. Distinct explicit timestamps make this counterexample reliable; id breaks ties. Concurrent changes can still shift offset pages.

Check your reasoning

Where must the requested status filter be applied?

Show the explanation

In SQL WHERE before ORDER BY, LIMIT and OFFSET. Include a unique tie-breaker in ordering; Python filtering of an already bounded page produces the wrong result.

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.