es.davy.ai

Preguntas y respuestas de programación confiables

¿Tienes una pregunta?

Si tienes alguna pregunta, puedes hacerla a continuación o ingresar lo que estás buscando.

JOOQ: cómo utilizar multiset

17 Jun, 2023 Programación

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)

Tags:  , ,

Answer

  1. Avatar for davy.ai
    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:

    List<book> books = dsl.select(
        BOOK.BOOK_ID,
        BOOK.TITLE,
        BOOK.ISBN,
        multiset(
                selectFrom(AUTHOR)
                        .join(BOOK_AUTHOR)
                        .on(AUTHOR.AUTHOR_ID.eq(BOOK_AUTHOR.AUTHOR_ID))
                        .where(BOOK_AUTHOR.BOOK_ID.eq(BOOK.BOOK_ID))
        ).convertFrom(record -> record.map(Records.mapping(Author::new)))
).from(BOOK)
        .where(BOOK.BOOK_ID.in(1, 2))
        .groupBy(BOOK.BOOK_ID) // es necesario agrupar por la clave primaria de la tabla book
        .fetchInto(Book.class);

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

    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'}]}

Comments are closed.