Search code examples
jpajoinspring-data-jpamany-to-manyjpql

JPQL ManyToMany Join based on codition

I need help with a JPQL Query.

I have the Entity Author and the Entity Book, they are related as ManyToMany relation in JPA, where the Author has the owning side. What I need is a query to get all Books that were not written by a certain Author.

I tried already writhing a query but it does not work.

Select b From Book b Where (Select a From Author a where a.AuthorId = :authorId) Not Member of b.authors) Or b.authors Is Empty);

What am I missing for the query to work properly? Thanks in advance for your help.

Solution

  • Try one of these, it should actually work, but it's gonna be more helpful when you share the errors you get.

    SELECT b FROM Book b JOIN b.authors authors WHERE (:authorId NOT IN authors) OR (:authorId Is Null);

    Or

    SELECT b FROM Book b JOIN b.authors authors WHERE (:authorId NOT IN b.authors) OR (:authorId Is Null);

    Hope it could help you.