One-to-many select in Jooq

I am trying out JOOQ and trying to select from 3 tables (Author, Books and Articles) using a join statement. The ERD is as follows:

Author ----< Books
   |
   |
   ^
Articles

The query I have is the following:

    final List<Tuple3<AuthorRecord, BooksRecord, ArticlesRecord>> tupleList =
        persistenceContext.getDslContext()
            .select()
            .from(Author.AUTHOR)
            .join(Books.BOOKS)
            .on(Author.AUTHOR.ID.eq(Books.BOOKS.AUTHOR_ID))
            .join(Articles.ARTICLES)
            .on(Author.AUTHOR.ID.eq(Articles.ARTICLES.AUTHOR_ID))
            .where(Author.AUTHOR.ID.eq(id))
            .fetch()
            .map(r -> Tuple.tuple(r.into(Author.AUTHOR).into(AuthorRecord.class),
                r.into(Books.BOOKS).into(BooksRecord.class),
                r.into(Articles.ARTICLES).into(ArticlesRecord.class)));

I also have a protobuf object as follows:

message Author {
    int64 id = 1;
    string name = 2;
    repeated string books = 3;
    repeated string articles = 4;
}

(or any other pojo for that matter) which will hold all the entities (author details + list of books + list of articles) into one object. My question is, is there some way to map out of the box all three tables into one object using JOOQ.

Thanks in advance.

Answer

Using JOIN doesn’t work for this.

Your query will be rather inefficient because if you’re using joins this way, you’re creating a cartesian product between the books and the articles table, resulting in quite some memory and CPU consumption both in the database and in your Java client, before you de-duplicate all the meaningless combinations.

The “correct” SQL approach would be to use MULTISET as described in this article here. Unfortunately, jOOQ 3.9 doesn’t support MULTISET yet (nor do many databases). So, you should create two separate queries:

1. Fetching all the books
2. Fetching all the articles

And then use something like Java 8 Streams to map them into a single object.

Using MULTISET starting from jOOQ 3.15

Luckily, starting from jOOQ 3.15, there’s an out-of-the-box solution to nesting collections in SQL using MULTISET. Your query would look like this:

Using reflection

List<Author> authors =
ctx.select(
      AUTHOR.ID,
      AUTHOR.NAME,
      multiset(
        select(BOOKS.TITLE)
        .from(BOOKS)
        .where(BOOKS.AUTHOR_ID.eq(AUTHOR.ID))
      ).as("books"),
      multiset(
        select(ARTICLES.TITLE)
        .from(ARTICLES)
        .where(ARTICLES.AUTHOR_ID.eq(AUTHOR.ID))
      ).as("articles")
    )
   .from(AUTHOR)
   .where(AUTHOR.ID.eq(id))
   .fetchInto(Author.class);

Using type safe, ad-hoc conversion

List<Author> authors =
ctx.select(
      AUTHOR.ID,
      AUTHOR.NAME,
      multiset(
        select(BOOKS.TITLE)
        .from(BOOKS)
        .where(BOOKS.AUTHOR_ID.eq(AUTHOR.ID))
      ).as("books").convertFrom(r -> r.map(Record1::value1)),
      multiset(
        select(ARTICLES.TITLE)
        .from(ARTICLES)
        .where(ARTICLES.AUTHOR_ID.eq(AUTHOR.ID))
      ).as("articles").convertFrom(r -> r.map(Record1::value1))
    )
   .from(AUTHOR)
   .where(AUTHOR.ID.eq(id))
   .fetch(Records.mapping(Author::new));

For more information about MULTISET, please refer to this blog post, or the manual sections:

• MULTISET value constructor
• MULTISET_AGG

Using SQL/XML or SQL/JSON starting from jOOQ 3.14

Starting from jOOQ 3.14, you can nest collections via SQL/XML or SQL/JSON, if your RDBMS supports that. You can produce a document, and then use something like Gson, Jackson, or JAXB to map it back to your Java classes. For example:

List<Author> authors =
ctx.select(
      AUTHOR.ID,
      AUTHOR.NAME,
      field(
        select(jsonArrayAgg(BOOKS.TITLE))
        .from(BOOKS)
        .where(BOOKS.AUTHOR_ID.eq(AUTHOR.ID))
      ).as("books"),
      field(
        select(jsonArrayAgg(ARTICLES.TITLE))
        .from(ARTICLES)
        .where(ARTICLES.AUTHOR_ID.eq(AUTHOR.ID))
      ).as("articles")
    )
   .from(AUTHOR)
   .where(AUTHOR.ID.eq(id))
   .fetchInto(Author.class);

Note that JSON_ARRAYAGG() aggregates empty sets into NULL, not into an empty []. If that’s a problem, use COALESCE()