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?
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments