Menu
I've the following DB model:
Category -< ProductCategory >- Product -< Variant
(Category has many-to-many relationship with Product and Product has one-to-many relationship with Variant)
Now I need to get all Category records that have product with active variants. I'm getting these objects via the following JPQL query:
@Query("select distinct c from Category c join c.products as p join p.variants as pv where pv.active = true")
It works well - returns categories accurately - however every single Category contains all the products - not only these with active variants.
How can I filter out the products (or variants) that are inactive in a single query?
Here's a postgres script that with database struct and sample data. For given data two categories (CAT 1, CAT 2), two products (PROD 1, PROD 2) and three variants (VAR 1, VAR 2, VAR 3) should be returned.
637
If you execute only one SQL you can upgrade Parent class and Jpa query below. @Query("select new Parent(c.parent.id, c.parent.name, c.id, c.name) from Child c where c.name = 'child1'") List<Parent> getParent(); Hibernate generates a single query.
In JPQL, JOIN can only appear in a FROM clause. The INNER keyword is optional (i.e. INNER JOIN is equivalent to JOIN). c1 is declared as a range variable that iterates over all the Country objects in the database.
The @Query annotation can only be used to annotate repository interface methods. The call of the annotated methods will trigger the execution of the statement found in it, and their usage is pretty straightforward. The @Query annotation supports both native SQL and JPQL.
In order to define SQL to execute for a Spring Data repository method, we can annotate the method with the @Query annotation — its value attribute contains the JPQL or SQL to execute. The @Query annotation takes precedence over named queries, which are annotated with @NamedQuery or defined in an orm. xml file.
I had exactly the same problem and it took me a while to find out how this works. The child list should be filtered when you add FETCH after your JOIN like this:
SELECT DISTINCT c FROM Category c JOIN FETCH c.products as p join p.variants as pv where pv.active = true
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
