Given these 3 tables with a many-to-many relationship

book

book_author

author

And records

public record Book(Integer id, String title, String isbn, List<Author> authors) {}// don't need nr of pages

public record Author(Integer id, String name){}

How to get a List<Book> using multiset?

List<Book> books = dsl.select(
        BOOK_AUTHOR.BOOK_ID,
        select(BOOK.TITLE).from(BOOK).where(BOOK.BOOK_ID.eq(BOOK_AUTHOR.BOOK_ID)).asField("title"),
        select(BOOK.ISBN).from(BOOK).where(BOOK.BOOK_ID.eq(BOOK_AUTHOR.BOOK_ID)).asField("isbn"),
        multiset(
                selectFrom(AUTHOR).where(AUTHOR.AUTHOR_ID.eq(BOOK_AUTHOR.AUTHOR_ID))
        ).as("authors").convertFrom(record -> record.map(Records.mapping(Author::new)))
).from(BOOK_AUTHOR)
 .where(BOOK_AUTHOR.BOOK_ID.in(1, 2))
 .fetchInto(Book.class);

 books.forEach(System.out::println);

Expected a list with 2 books, but getting 3 books instead !?...

Book{id=1, title='JOOQ', isbn='1234', authors=[Author{name='Lucas'}]}
Book{id=2, title='SQL', isbn='2345', authors=[Author{name='Lucas'}]}
Book{id=2, title='SQL', isbn='2345', authors=[Author{name='Jose'}]}

What is the problem?

The generated SQL:

select "public"."book_author"."book_id", (select "public"."book"."title" from "public"."book" where "public"."book"."book_id" = "public"."book_author"."book_id") as "title", (select "public"."book"."isbn" from "public"."book" where "public"."book"."book_id" = "public"."book_author"."book_id") as "isbn", (select coalesce(jsonb_agg(jsonb_build_array("v0", "v1")), jsonb_build_array()) from (select "public"."author"."author_id" as "v0", "public"."author"."author_name" as "v1" from "public"."author" where "public"."author"."author_id" = "public"."book_author"."author_id") as "t") as "authors" from "public"."book_author" where "public"."book_author"."book_id" in (1, 2)

Used:

• JAVA 17
• POSTGRESQL
• spring-boot (2.6.1) with spring-boot-starter-jooq
• JOOQ 3.15.4 (allows the use of the multiset function)
• JOOQ code generation (maven plugin)

CodePudding user response：

The problem is that you are starting from BOOK_AUTHOR that has 3 entries. You should select from BOOK:

List<Book> books = dsl.select(
   BOOK.BOOK_ID,
   BOOK.TITLE,
   BOOK.ISBN,
   multiset(
     select(AUTHOR.AUTHOR_ID,AUTHOR.AUTHOR_NAME)
         .from(AUTHOR).innerJoin(BOOK_AUTHOR)
         .on(BOOK_AUTHOR.AUTHOR_ID.eq(AUTHOR.AUTHOR_ID))
         .where(BOOK_AUTHOR.BOOK_ID.eq(BOOK.BOOK_ID))
    ).as("authors")
     .convertFrom(record -> record.map(Records.mapping(Author::new)))
    ).from(BOOK)
     .where(BOOK.BOOK_ID.in(1, 2))
     .fetchInto(Book.class);