Fetch grouped POJOs using multiset in JOOQ

Take the usual example of the book and author tables in a many-to-many (book_author) relationship. If I want to fetch a list of books for each author ID, specifically using multiset*, based on this example I'd expect it to look like this:

select(
        AUTHOR.ID,
        multiset(
            select(BOOK_AUTHOR.book())
            .from(BOOK_AUTHOR)                                        
            .where(BOOK_AUTHOR.AUTHOR_ID.eq(AUTHOR.ID)))
            .as("books").convertFrom(r -> r.map(mapping(Book::new)))
        .from(AUTHOR)
        .fetchMap(...)

But the mapper receives Record1<BookRecord> and expects BookRecord. So I instead have to write nesting mapping:

dsl.select(
        AUTHOR.ID,
        multiset(
            select(BOOK_AUTHOR.book())
            .from(BOOK_AUTHOR)
            .where(BOOK_AUTHOR.AUTHOR_ID.eq(AUTHOR.ID)))
            .as("books")
            //👇 seems wrong
            .convertFrom(r -> r.map(mapping((b) -> mapping(Book::new).apply(b)))))
        .from(AUTHOR)
        .fetchMap(AUTHOR.ID, mapping((a, b) -> b));

Is this the most direct/obvious way to express this multiset query?

EDIT:

Here's my improved version after getting my earlier question answered (still not what the example promised, though):

dsl.select(
    AUTHOR.ID,
    multiset(
            select(BOOK_AUTHOR.book().mapping(Book::new)) //👈
            .from(BOOK_AUTHOR)
            .where(BOOK_AUTHOR.AUTHOR_ID.eq(AUTHOR.ID)))
            .as("books").convertFrom(r -> r.map(Record1::value1)))
    .from(AUTHOR)
    .fetchMap(AUTHOR.ID, Record2::value2);

*I'm aware I don't really need multiset here. This is a spin-off from my previous question on grouping POJOs, and I'm just exploring the options.

Solution

Why did the records get nested

The root problem of any issues related to generics is located here:

select(BOOK_AUTHOR.book())

You're nesting a BookRecord in your SELECT clause, effectively producing a subquery of type Select<Record1<BookRecord>>, when you expected this, instead: Select<BookRecord>. I.e. you probably expected a "flat" result, not a nested one. Perhaps, it makes more sense if you add more columns to the projection? E.g. if you did this:

select(BOOK_AUTHOR.BOOK_ID, BOOK_AUTHOR.book())

Then it would be more obvious that your resulting type is Select<Record2<Long, BookRecord>>.

How to avoid the nesting

Obviously, in your specific example, you want:

No such unnecesary nesting
Profit from the implicit join in BOOK_AUTHOR.book()

Starting from jOOQ 3.19, to-many path expressions will be available, including explicit path expressions in the FROM clause:

#13639 Add support for to-many path expressions
#14985 Allow for specifying explicit path joins

It will then be possible to write something like this:

multiset(
  selectFrom(AUTHOR.book()) // Implicit correlation here on many-to-many relationship
)

// Simple mapping available now:
.convertFrom(r -> r.map(mapping(Book::new)))

Key here is that selectFrom() produces a type-safe TableRecord (in this case BookRecord), but doesn't nest it.

In jOOQ 3.18 or less, this is not yet available, so your 2 workarounds are probably the simplest approach, knowing that this situation arises because you are nesting records in SELECT.

You could obviously always avoid the nesting by spelling out the projection explicitly:

multiset(
  select(BOOK_AUTHOR.book().ID, BOOK_AUTHOR.book().TITLE, ...)
  ...
)

In case of which this problem would be avoided. While your desired approach would be convenient, it would also likely project too many columns. Do you really need all of them? The overhead (memory consumption, transfer, etc.) can be significant.