You need one row per order carrying the customer's name and the number of line items on that order. How do you shape that as a JPQL DTO projection, and what goes wrong when a projection spans a to-many association?

## The cardinality rule A projection produces **one object per SQL result row**. Everything about flat projections follows from that. - A **to-one** association (`@ManyToOne`, `@OneToOne`) does not change the row count, so its columns flatten straight into the DTO. - A **to-many** association multiplies the row count by the number of children, so anything you project alongside it is repeated. ## The to-one side ```java select new com.x.OrderRow(o.id, o.createdAt, c.name, c.country, o.total) from Order o join o.customer c ``` One join, one row per order, and the SQL selects exactly the five columns named. Two details worth stating: **Explicit join versus path navigation.** `o.customer.name` inside the constructor also works and renders a join — but always an **inner** join. If `customer` is optional, that silently drops orders without one. Write `left join o.customer c` when the association is nullable and you want those rows. **Depth.** `join o.customer c join c.address a` flattens two levels the same way. Each additional to-one join adds a table but not a row. ## The to-many side This is where projections go wrong. Consider: ```java select new com.x.OrderRow(o.id, c.name, l.sku) -- one row per LINE from Order o join o.customer c join o.lines l ``` An order with four lines produces four `OrderRow`s. Callers who expected one per order now double-count totals — a defect that survives testing on a fixture where every order has one line. Three correct responses: **1. Aggregate.** If what you need is a number, compute it in SQL: ```java select new com.x.OrderRow(o.id, c.name, count(l), coalesce(sum(l.qty), 0)) from Order o join o.customer c left join o.lines l group by o.id, c.name ``` Use `left join` so orders with zero lines survive with `count = 0`. Every non-aggregated select item must appear in `group by` (some databases relax this when grouping by a primary key; do not rely on it portably). Beware the classic double-aggregate trap: joining **two** independent to-many collections and summing both inflates each by the other's cardinality — compute them in separate queries or with subqueries in the select list. **2. A correlated scalar subquery.** Sometimes clearer, and avoids `group by` entirely: ```java select new com.x.OrderRow(o.id, c.name, (select count(l) from Line l where l.order = o)) from Order o join o.customer c ``` **3. Two queries.** When you need the actual children rather than a number, no constructor expression can help — a DTO holding a `List<LineRow>` cannot be built one row at a time. Run a flat query for the parents, then a second query `where l.order.id in :ids` for the children, and group them in memory (`Collectors.groupingBy`). Two queries with bounded result sets, no N+1, no row multiplication. Keep the `in` list chunked (typically a few hundred to a couple of thousand ids) so the second statement stays plan-friendly. ## Why `distinct` is not the answer Reaching for `select distinct` when rows multiply is the reflex to unlearn. `distinct` compares the **selected values**; rows that differ by `l.sku` are distinct, so nothing collapses. And when it does collapse rows, it does so by making the database sort or hash the whole result — cost paid to undo a join you should not have made. (`distinct` on an *entity* fetch-join query is a different, legitimate use — and that belongs to the fetch-join discussion, not here.) ## Ordering and paging Because a flat projection is one row per result object, `setMaxResults` means exactly what it says — unlike a to-many fetch join, where limiting rows truncates children. This is a real advantage of projections for paginated screens: the SQL can carry `limit`/`offset` and the count is honest. Add `order by` on a deterministic column (include the id as a tiebreaker) or paging across requests will shuffle. ## Nested DTO shapes JPA 3.2 and Hibernate permit a nested `new` for **to-one** structure — `new OrderRow(o.id, new CustomerRow(c.id, c.name))` — which stays one row per order. There is still no way to nest a *collection*, and there is no partial-entity trick that makes it safe: the moment you want children, it is the two-query pattern. ## What to say in an interview State the cardinality rule first, then map each association kind to its treatment, then name the row-multiplication failure and why `distinct` does not fix it. Mentioning `left join` for optional to-ones and for zero-count aggregates is the detail that distinguishes someone who has actually shipped this from someone reciting syntax.