JPA - how to prevent an unnecessary join while querying many to many relationships

Question

I have entities Product and Category that are joined with a simple many to many relationship. I want to get a paginated list of products filtered by a single category. I'm trying to write a Spring Data JPA automatic method or a JPQL query method that would produce the SQL similar to the following:

select [...] FROM ProductToCategory ptc INNER JOIN Product p ON ptc.product_id=p.id WHERE ptc.category_id=?  LIMIT ? OFFSET ?

Since the ProductToCategory join table isn't a JPA entity and I can't reference it in JPQL, the closest thing I could come up with was:

@Query("SELECT p FROM Category c INNER JOIN c.products p WHERE c=:category")
Page<Product> findByCategories(@Param("category") Category category, Pageable pageable);

But the resulting SQL produces a redundant join with the Category table and applies the where clause there, instead of on category id in the ProductToCategory table. Is there a way to do this without resorting to native SQL?

Answer 1

Only way I can see is to map an entity to the join table and replace the many-to-many Product<>Category with one-to-many relationships pointing to this new entity from both Product and Category.

Such a change would actually be in line with Hibernate best practices:

https://docs.jboss.org/hibernate/orm/3.3/reference/en/html/best-practices.html

Do not use exotic association mappings: Practical test cases for real many-to-many associations are rare. Most of the time you need additional information stored in the "link table". In this case, it is much better to use two one-to-many associations to an intermediate link class. In fact, most associations are one-to-many and many-to-one. For this reason, you should proceed cautiously when using any other association style.