How to filter a child collection in a JPQL query?

Question

I have the following DB model:

Category -< ProductCategory >- Product -< Variant

( Categorymany-to-many s) Productand Productone-to-many s Variant)

Now I need to get all the records Categorycontaining the product with active options. I get these objects using 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 - it returns categories exactly - however, each one Categorycontains all the products - not just using active options.

How can I filter products (or options) that are inactive in a single request?

Here's a postgres script that has a database and data samples. For these data, you need to return two categories (CAT 1, CAT 2), two products (PROD 1, PROD 2) and three options (VAR 1, VAR 2, VAR 3).

Answer 1

I had exactly the same problem and it took me a while to find out how it works. The kids list should be filtered if you add FETCHafter your JOINas follows:

SELECT DISTINCT c FROM Category c JOIN FETCH c.products as p join p.variants as pv where pv.active = true