Как получить список авторов с их книгами и тегами в одном SQL-запросе и какие есть варианты

Что проверяют

SQL здесь только половина. Java-разработчику важнее показать, как тот же граф грузится через JPA, и знать про N+1 и размножение строк. Следом спрашивают, почему нельзя просто поставить FetchType.EAGER на обе связи.

Как отвечать

На уровне SQL три варианта:

• плоский JOIN: authors LEFT JOIN books LEFT JOIN book_tags LEFT JOIN tags, строка на пару книга и тег, дерево собирается в коде;
• агрегация: string_agg или json_agg в PostgreSQL, GROUP_CONCAT или JSON_ARRAYAGG в MySQL, по строке на книгу или автора;
• несколько запросов: авторы, затем книги по списку идентификаторов через IN, затем теги. Без дублей, три обращения к базе.

Теперь JPA. Если пройти по author.getBooks() и book.getTags() в цикле, получится N+1: запрос на каждого автора и каждую книгу. Что с этим делать:

• JOIN FETCH: select a from Author a left join fetch a.books b left join fetch b.tags. Hibernate 6 сам убирает дубли корневых сущностей, distinct для этого не нужен. Но две коллекции типа List в одном fetch дают MultipleBagFetchException, помогает Set или два запроса;
• @EntityGraph: то же декларативно в репозитории Spring Data;
• @BatchSize или hibernate.default_batch_fetch_size: ленивые коллекции догружаются пачками через IN, и N+1 превращается в несколько запросов;
• DTO-проекция: если данные нужны только для отображения, выберите плоские поля в record и соберите структуру сами.

С пагинацией осторожно: setMaxResults вместе с JOIN FETCH коллекции Hibernate применяет в памяти, с предупреждением в логе. Правильнее сначала выбрать страницу идентификаторов авторов, затем догрузить граф по ним.

Где спотыкаются

Ставят FetchType.EAGER. N+1 никуда не уходит, просто запросы летят всегда, даже когда граф не нужен.

Делают JOIN FETCH двух коллекций и получают либо исключение, либо взрыв строк: у автора 10 книг по 10 тегов, и на одного автора приходит сотня строк.

Используют внутренний JOIN и теряют авторов без книг, хотя в задаче нужен полный список.