skip to content

Why does a grouped total or a row spanning two aggregates need a projection rather than a mapped-object query?

level: middleimportance: should knowfreq 48%

answer

  1. no mapped class owns a grouped row
  2. one row per group, computed columns
  3. the use case names the shape
  4. fold once in the engine, not per row

basics

~20 s

A mapped-object query returns instances of one mapped class per row. A grouped row and a row combining fields from two aggregates correspond to no mapped class, so a projection is the only result the layer can build for them.

solid answer

~40 s

Mapped classes describe rows that exist in a table. A grouped result does not: it has one row per group, and its columns are computed - a count, a sum, a maximum - so no mapped class owns them. A row that mixes fields from two different aggregates is the same problem from the other side; returning it as objects means returning both graphs and folding them together in memory, which reads far more than the screen shows and puts the joining logic in the wrong place. Projecting lets the statement produce exactly the row the use case displays, with the aggregation done once by the engine, so the result scales with the number of groups rather than with the number of underlying rows.

code

sql · 7 lines
sql
SELECT c.id,
       c.name,
       COUNT(o.id)  AS order_count,
       SUM(o.total) AS lifetime_value
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name

go deeper

for a junior

Know the basic fact: totals and counts come back as values, not as objects, because no table row looks like them. You ask the database for the summary rather than adding up rows yourself.

for a middle

Explain the growth-rate argument - work scaling with groups instead of rows - and say why a row combining two aggregates needs a type of its own rather than two object graphs.

for a senior

Show that you check correctness as well as cost: grouping across a to-many join inflates sums, and a cross-aggregate read couples this screen to another aggregate's tables.

for a principal

Own the boundary question: how many use-case-shaped read types the codebase should carry, who owns them, and when a read that crosses aggregates is a signal about the model rather than a query to write.

## A stored row and a result row are not the same thing A mapping layer's central assumption is that a class corresponds to a row shape it can identify, load and write. That assumption is what makes tracking possible - the layer knows the key, so it knows what to update. Plenty of results a real application needs break it: - **Aggregated rows.** `SELECT status, COUNT(*) FROM orders GROUP BY status` produces one row per status. There is no `status-and-count` table, no key, and nothing to write back. - **Rows spanning aggregates.** A screen shows a customer's name beside their most recent order date and their lifetime total. Three sources, one row, owned by no single class. - **Computed columns.** A rank, a difference between two dates, a bucketed value. These exist only in the result. For all of them, the honest answer is that the result is a **query shape**, not a stored shape - and a projection is how a data-access layer expresses a query shape. ## Why not just load the objects and fold in memory You can always load the customers, load their orders, and compute the totals in application code. Compare the two paths: | | Load and fold in memory | Project with grouping | |---|---|---| | Rows transferred | Every underlying row | One per group | | Work scales with | Rows | Groups | | Index use | Only for the filter | Filter, grouping and covering columns | | Tracking cost | One tracked object per row | None | | Where the rule lives | Application code | The statement | | Debuggability | Step through code | Read the statement and its plan | The first two lines are usually decisive. A tenant with two customers and four million order rows makes the in-memory fold read four million rows to produce two. The engine can do the same fold in a single pass, often straight off an index, and hand back two rows. This is not a micro-optimisation; it is a difference in growth rate. The cases where folding in memory still wins are real but narrow: the set is small and bounded, the rule is genuinely awkward to express in a statement, or the same loaded rows are needed for something else anyway. ## Rows that cross an aggregate boundary A data-access interface built around one aggregate can return that aggregate's objects. It cannot return a row that is half another aggregate without either pulling the other graph in whole or inventing a type for the combination. Inventing the type is the correct move - the combination is exactly what the use case is about, and naming it after the use case ("the row this list shows") is clearer than pretending it is a domain object. Two consequences follow: - The projection type belongs to the read use case, not to the domain model. It is allowed to be flat, allowed to be denormalised, and allowed to disappear when the screen does. - The statement now joins across the boundary. That is a deliberate coupling in the read path, and it is worth writing down: it is the reason a change to the other aggregate's table can break this screen. ## What grouping in the statement does not give you - **It does not make the read cheap by itself.** Grouping over a join can be far more expensive than grouping over one table, and grouping over a to-many join can produce wrong totals - each side's rows multiply, so sums count the same rows repeatedly. Group over a single fan-out or aggregate the sides separately. - **It does not give you a writable result.** Grouped rows have no key and no write path, by definition. - **It does not replace the model's rules.** If the total the screen shows is defined by domain logic - excluding certain statuses, applying a rounding rule - that logic now appears in the statement as well as on the model, and the two can drift. ## A practical way to decide 1. Write down the row the screen actually shows, column by column. 2. Ask which mapped class that row is. If the honest answer is "none", the result is a projection - stop trying to force it through the mapped path. 3. Ask where the aggregation should happen. If the number of underlying rows is much larger than the number of result rows, it belongs in the statement. 4. Ask whether the row crosses a to-many link more than once. If it does, split the statement rather than accepting inflated aggregates.

  • Should a projection type that spans two aggregates live with the domain model?
    No - it belongs to the read use case that asked for it. It is a flat carrier named after the screen or the report, free to denormalise, and safe to delete when that use case goes. Putting it in the domain model invites someone to add behaviour to a type that has no invariants to protect.
  • When is folding rows in application code still the better call?
    When the set is small and bounded, when the rule is genuinely hard to express in a statement, or when the rows are already being loaded for another purpose. The test is the ratio: if the underlying rows vastly outnumber the result rows, the fold belongs in the engine.

saying these in an interview costs you the question

  • Loads every child row to compute a total in application code
  • Insists every result must map to a mapped class
  • Groups over two to-many joins and trusts the sums
  • Treats a grouped result as something that can be written back
  • Puts the cross-aggregate read type into the domain model