Hi Jason,
There has been a very similar request just recently on Stack Overflow:
https://stackoverflow.com/q/50192902/521799
At some point, maybe using the Stream API and Collectors might be the way
to go. E.g.
Map<APojo, Map<BPojo, CPojo>> result =
using(configuration()).select(A.fields())
.select(B.fields())
.select(C.fields())
.from(A)
.leftJoin(B).on(B.aId.eq(A.aId))
.leftJoin(C).on(C.cId.eq(B.cId))
.fetch()
.stream()
.collect(Collectors.groupingBy(
r -> r.into(A).into(APojo.class),
Collectors.toMap(
r -> r.into(B).into(BPojo.class),
r -> r.into(C).into(CPojo.class))));
Or with the upcoming jOOQ 3.11, the collect() shortcut method can be used:
Map<APojo, Map<BPojo, CPojo>> result =
using(configuration()).select(A.fields())
.select(B.fields())
.select(C.fields())
.from(A)
.leftJoin(B).on(B.aId.eq(A.aId))
.leftJoin(C).on(C.cId.eq(B.cId))
.collect(Collectors.groupingBy(
r -> r.into(A).into(APojo.class),
Collectors.toMap(
r -> r.into(B).into(BPojo.class),
r -> r.into(C).into(CPojo.class))));
Notice that in this solution, the parent -> child graph is only a single
branch: A -> B -> C. If you want to materialise a parent with two
independent children, e.g. B <- A -> C, things will get a bit more
difficult, because with ordinary joins, there will be a cartesian product
between B x C, which will
1. Lead to wrong results
2. Produce performance issues
Either, you'd have to run two separate queries, or if you're using a
database like PostgreSQL, array aggregation might help (or MULTISET in
Oracle).
2018-05-08 0:54 GMT+02:00 Jason Bennett <[email protected]>:
> If I have a main table, and a child table, it's fairly easy for me to use
> fetchGroups() to join the two tables and get back a map of the parent to a
> list of children. Is there a method in jOOQ that goes further, and allows
> me to fetch multiple different child tables in a single query?
>
> I considered using fetch(), iterating through the records, and then
> fetchInto(), but intoList() does not take a type argument (which I think
> would be required to do what I want).
>
> --
> You received this message because you are subscribed to the Google Groups
> "jOOQ User Group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
