JOOQ: cómo utilizar multiset
Dadas estas tres tablas con una relación muchos a muchos:
book
book_id | title | isbn | num_pages |
---|---|---|---|
1 | JOOQ | 1234 | 123 |
2 | SQL | 2345 | 155 |
book_author
book_id | author_id |
---|---|
1 | 1 |
2 | 1 |
2 | 2 |
author
author_id | author_name |
---|---|
1 | Lucas |
2 | Jose |
Y los registros:
public record Book(Integer id, String title, String isbn, List<author> authors) {}
public record Author(Integer id, String name){}
¿Cómo obtener una List<book>
usando el 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);
¿Cuál es el problema?
El SQL generado es:
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)
Se espera una lista con 2 libros, pero en su lugar se están obteniendo 3 libros. ¿Por qué?
Se ha utilizado:
– JAVA 17
– POSTGRESQL
– spring-boot (2.6.1) con spring-boot-starter-jooq
– JOOQ 3.15.4 (permite el uso de la función multiset
)
– Generación de código JOOQ (plugin para Maven)
davy.ai
El problema es que la consulta generada por JOOQ es correcta y devuelve el resultado esperado. Sin embargo, el modo en que funciona la función
multiset
en JOOQ es creando un producto cartesiano de la consulta principal y la subconsulta, lo que puede resultar en varias filas con la misma clave primaria (en este caso,book_id
).Para evitar este problema, es necesario agrupar las filas por la clave primaria de la tabla
book
, utilizando algo como lo siguiente:Esta consulta agrupará los resultados por
book_id
, evitando duplicados, y devolverá el resultado esperado:Book{id=1, title='JOOQ', isbn='1234', authors=[Author{name='Lucas'}]}
Book{id=2, title='SQL', isbn='2345', authors=[Author{name='Lucas'}, Author{name='Jose'}]}