JPQL SELECT where more than one n:m per left-table entry exists

Question

I did an JPA n:m structure where books(id, name) and authors(id, lastname) has a n:m Relation with a self-made table (bookAuthors) inbetween which has (book.id, authors.id) as combined pk.

Now I want to do a JPQL Querie where all books with more then one author will be returned.

I tried a few SELECTs but none of them worked. So you have an idea how to JPQL-SELECT auch a query?

Thanks in advance Sut

Answer 1

If you configured your entity correctly i presume your entity looks like following:

Book.java

...
public class Book {
    
    @Id
    ... other related annotation
    private Long bookId;

    // other related properties like title, publishedAt, numberOfPages etc
    
    @ManyToMany(...)
    private List<Author> authors;    
}

Author.java

...
public class Author {
    
    @Id
    ... other related annotation
    private Long authorId;

    ... other related properties name, gender, age etc.

    @ManyToMany(...)
    private List<Book> books;
}

Then your BookRepository.java should contain a method like following

BookRepository.java

@Repository
public interface BookRepository extends JPARepository<Book, Long> {
    
    @Query("SELECT book FROM Book book"
           + " WHERE SIZE(book.authors) > 1")
    public List<Book> bookWithMultipleAuthor();
}