How to use JPQL join on unidirectional one to many relationship

Question

Here are my entities:

@Entity
public class Author {

    @Id
    private Long id;

    //...
}

and

@Entity
public class Book {

    @Id
    private Long id;

    @ManyToOne(optional = true)
    @JoinColumn(name = COLUMN_AUTHOR_ID, referencedColumnName = "id")
    private Author author;

    //...
}

I don't want to declare the Set<Book> books field in the Author entity to avoid unnecessary data fetch. I already know about fetch = FetchType.LAZY, but I have some cases in which even the lazy fetching is triggered.

Here is my question: How can I use JPQL join to retrieve the relationship in a custom query? Look at ??books?? in the below query:

entityManager.createQuery("SELECT new " + AuthorWithBooks.class.getName() +
                "(a.id, ..., group_concat(b.name)) FROM Author a LEFT JOIN a.??books?? b GROUP BY a.id", AuthorWithBooks.class);

You should consider that there might be some Author with no book and I want to include them in my query! So I can not start my join from Book.

Thanks

Simon Martinelli Simon Martinelli · Accepted Answer · 2022-07-28T09:02:21

Simply switch the from and join clause:

entityManager.createQuery("SELECT new " + AuthorWithBooks.class.getName() +
            "(a.id, ..., group_concat(b.name)) FROM Book b " + 
            "RIGHT JOIN b.author a GROUP BY a.id", AuthorWithBooks.class);

How to use JPQL join on unidirectional one to many relationship

1 Answers