skip to content

In Active Record, why does Order.includes(:line_items).where(line_items: { backordered: true }) leave each order holding only its backordered line items?

level: middleimportance: should knowfreq 42%

answer

  1. the where hash forces a join
  2. joined rows build the association
  3. association marked loaded with a subset
  4. orders without a match drop out
  5. filter with joins, load with preload

basics

~20 s

The where hash makes includes eager load through one LEFT OUTER JOIN, and Active Record builds each order's line_items from the joined rows that passed the filter, so the association is marked loaded holding only the backordered items.

solid answer

~40 s

A `where` hash on `line_items` references that table, so `includes` switches to `eager_load`: one statement, `LEFT OUTER JOIN line_items ... WHERE line_items.backordered = TRUE`. Active Record then assembles each order's `line_items` from the rows that came back and marks the association loaded. Rows the WHERE removed never reach Ruby, so `order.line_items` shows a filtered subset and nothing re-queries it. The same WHERE also drops orders with no backordered item, because their outer-joined columns are NULL. The `includes` API documentation calls this out: conditions on an included association filter both sides. To list orders that have a backordered item while loading all their items, filter and load separately: `Order.where(id: LineItem.where(backordered: true).select(:order_id)).preload(:line_items)`, or `joins` for the filter plus `preload` for the load.

code

ruby · 6 lines
ruby
# Subset: each order.line_items holds backordered rows only
Order.includes(:line_items).where(line_items: { backordered: true })

# Full collections for the orders that have a backordered item
backordered_ids = LineItem.where(backordered: true).select(:order_id)
Order.where(id: backordered_ids).preload(:line_items)

go deeper

for a junior

Remember that a where hash on an included association filters what gets loaded into that association, not just which parent records come back.

for a middle

Explain the switch from separate queries to one LEFT OUTER JOIN and how the joined rows become a loaded, filtered collection.

for a senior

Rewrite the query so filtering and loading are separate steps, and audit callers that might receive partially loaded collections.

for a principal

Treat partially loaded associations as a correctness risk worth a team convention, such as scoped associations for subsets, rather than a performance detail.

## The bug as it shows up A wine-shop admin wants a **backorder queue**: orders that contain at least one backordered bottle, each shown with its full list of line items so the picker can see what else ships with it. A natural first draft is: ```ruby @orders = Order.includes(:line_items).where(line_items: { backordered: true }) ``` The page lists the right orders, but every order shows **only its backordered items**. Totals computed from `order.line_items` are wrong, and nobody sees the bottles that are in stock. ## Why it happens Three Active Record rules combine: 1. **A `where` hash keyed by another table records a reference to it.** `where(line_items: { ... })` tells the relation that `line_items` is used in the query. 2. **A referenced table switches `includes` to `eager_load`.** `Relation#eager_loading?` becomes true, so instead of two separate queries Active Record runs one `LEFT OUTER JOIN` with the condition in its WHERE clause. 3. **The joined rows populate the association.** Active Record walks the result rows, builds each order and pushes the line items it finds into `order.line_items`, then marks the association **loaded**. Rows removed by WHERE never arrive, so the loaded collection is a subset, and because it is marked loaded, reading it again does not query. The Rails API documentation states it directly: conditions on an included association affect both sides, returning only orders that match and including only the matching line items. There is a second effect. The condition sits in WHERE, not in the join's ON clause, so orders whose joined `line_items` columns are NULL (no items, or none backordered) are filtered out too. The outer join behaves like an inner join here. ## Fixes that keep the full collection The fix is to **separate filtering the orders from loading their items**: - **Subquery on the parent ids.** `Order.where(id: LineItem.where(backordered: true).select(:order_id)).preload(:line_items)`. The condition is on `orders.id`, so no association table is referenced, and `preload` loads every line item of the matching orders. - **`joins` to filter, `preload` to load.** `Order.joins(:line_items).where(line_items: { backordered: true }).distinct.preload(:line_items)`. The INNER JOIN does the filtering, `distinct` collapses the repeated order rows, and `preload` always runs its own query, unaffected by the join's WHERE. - **A scoped association when you want both views.** Declaring a second association that carries the condition, then preloading both, gives `order.backordered_line_items` and the full `order.line_items` side by side. Two traps around the fixes: - Swapping in `preload` alone (`Order.preload(:line_items).where(line_items: { ... })`) fails: `preload` never joins, so the database rejects the unknown table and Active Record raises `ActiveRecord::StatementInvalid`. - Swapping in `eager_load` changes nothing: it is the join the bug already used. ## Spotting it in review The bug passes most tests, because a fixture order often has only matching items. Signals worth flagging in a code review: - an `includes` and a `where` hash naming the **same association table** in one relation; - a `references` call next to `includes`, which forces the same join; - a filtered relation whose records are later passed to code that sums, renders or mails `order.line_items`; - a test that builds an order with only backordered items, which cannot reveal a missing in-stock line. A test that gives one order a backordered and an in-stock bottle, then asserts both appear, pins the behaviour down. ## When the filtered subset is what you want Sometimes it is: a report that shows only the backordered items per order is exactly this query. The point is to choose it on purpose and never to hand such records to code that expects the whole collection, such as an order-total method or a mailer. | Relation | Orders returned | `order.line_items` holds | |---|---|---| | `includes(:line_items).where(line_items: { backordered: true })` | orders with a backordered item | backordered items only | | `where(id: LineItem.where(backordered: true).select(:order_id)).preload(:line_items)` | orders with a backordered item | all items | | `includes(:line_items)` | all orders | all items | The middle-level answer names the switch to a join, explains that the joined rows become the loaded association, and offers a filter-then-preload rewrite.

  • Could you fix it by calling order.line_items.reload in the view?
    It would show the full list, but `reload` runs one query per order, which brings back the per-row queries eager loading was meant to remove. Fix the relation instead: filter the orders with a subquery or a join and load the items with `preload`.
  • Why does the joins-plus-preload version need distinct?
    An INNER JOIN on `line_items` returns one order row per matching item, so an order with three backordered bottles appears three times. `distinct` collapses those rows to one order each; the separate `preload` query then loads every line item for the remaining order ids.

  • Order.includes(:line_items) always loads the full collection whatever the where says
  • Switching includes to eager_load keeps every line item
  • Switching to preload with the same where hash fixes it
  • Orders without a matching line item still appear because the join is LEFT OUTER
  • Calling reload on each association is an acceptable fix