Load one-to-many graphs with a grouped select terminal (resultGroupedBy)

[zantvoort]

Loading a one-to-many graph (parents with their child collections) currently requires manual assembly: query the many side, then group in memory. The idiom works but every caller rewrites it, and hydration re-materializes the parent graph for every child row.

val rows = pets.select().where(Pet_.owner.city.id eq cityId).orderBy(Pet_.owner.id).resultList
val byOwner = LinkedHashMap<Long, Pair<Owner, MutableList<Pet>>>()
for (pet in rows) {
    byOwner.getOrPut(pet.owner.id) { pet.owner to mutableListOf() }.second.add(pet)
}

Proposal

A grouped terminal operation on the query builder. The metamodel path names the parent; the result is an ordered map from parent to its children.

val ownersWithPets: Map<Owner, List<Pet>> = orm.entity<Pet>()
    .select()
    .where(Pet_.owner.city eq city)
    .orderBy(Pet_.owner)
    .resultGroupedBy(Pet_.owner)

Java: getResultGroupedBy(Pet_.owner).

Semantics

• The argument must be an eagerly fetched @FK entity path from the root. This is enforced at compile time: the generated metamodels type eager fields as TypedMetamodel<T, V, V> and Ref fields as TypedMetamodel<T, V, Ref<V>> (a new foundation interface implemented by AbstractMetamodel), and the signature requires component type == field type. Ref-mapped paths do not compile against resultGroupedBy; a runtime check backstops dynamically built paths.
• Returns an insertion-ordered Map<Owner, List<Pet>>; parents appear in row order (controlled by orderBy), children in row order within each group.
• where keeps its normal meaning: it filters child rows; parents appear only when at least one child matches (inner-join semantics).
• Every child's parent reference is the same instance as its map key.
• A ref-based variant resultGroupedByRef(path) (Java: getResultGroupedByRef) returns Map<Ref<V>, List<T>>: keys compare by primary key, keeping map lookups constant-cost, and for Ref foreign-key fields the group is taken directly from the foreign key without fetching the referenced record.

SQL

Unchanged. The query stays the join the user would write by hand, in line with the SQL-first principle (no GROUP BY, no aggregation functions injected):

SELECT p.id, p.name, p.birth_date, p.type_id, p.owner_id,
       o.first_name, o.last_name, o.address, o.telephone, o.city_id, c.name
FROM pet p
INNER JOIN owner o ON p.owner_id = o.id
INNER JOIN city c  ON o.city_id  = c.id
WHERE o.city_id = ?
ORDER BY o.id

Hydration

The performance gain is entirely client-side, building on the entity id-cache:

1. Per row, read the parent primary key first. On a cache hit, reuse the existing parent instance; duplicate parents cost one key decode plus a map lookup.
2. On a hit, skip decoding the parent graph columns entirely. JDBC drivers materialize column values lazily, so unread columns are transferred but never parsed.
3. Group into an insertion-ordered map keyed by parent PK, injecting the shared parent instance into each child record.

Because records are immutable, instance sharing is safe across rows, threads, and queries; no managed-entity state or snapshots are involved. This mirrors the dedup a JPA persistence context performs during a collection join fetch, without requiring one.

Motivation

The objectGraph workload in the benchmark suite (#214: 50 parents with 2 children each, PostgreSQL 17, loopback) shows the hand-grouped join in Storm measuring roughly 30% above the raw JDBC join baseline, with the redundant parent hydration as the main identified overhead. jOOQ's MULTISET avoids re-mapping through JSON aggregation; an id-cache achieves the same dedup inside the plain join without changing the SQL.

Out of scope (follow-ups)

• Parent-rooted variant including childless parents (LEFT JOIN semantics).
• Nested multi-level grouping.
• Database-side aggregation encodings (array_agg, jsonb_agg) as a dialect optimization behind the same API; current benchmark data does not justify the added SQL surface.

Tasks

• Core terminal getResultGroupedBy(Metamodel<T, V>) on the query builder returning an ordered Map<V, List<T>>
• Ref-based variant getResultGroupedByRef(Metamodel<T, V>) returning an ordered Map<Ref<V>, List<T>>
• Parent dedup via the entity id-cache during hydration; shared parent instance injected into children
• Skip parent column decoding on cache hits where the mapping layer permits
• Kotlin resultGroupedBy wrapper
• Docs: relationships.md and queries.md
• Switch the storm-benchmarks objectGraph workload to the new terminal once released