[FIXED] JPQL query for select parent Entity having @OneToMany relation with Child Entity, containing optional search parameters

Issue

I have two entities, Recipes and Ingredient:

Recipes looks like:

public class Recipes {

    private Long id;

    private String name;

    private Integer serveCount;

    private RecipesType type;

    @OneToMany(mappedBy = "recipes",
            cascade = CascadeType.ALL)
    private List<Ingredient> ingredients = new ArrayList<>();
}

And Ingredient looks like:

public class Ingredient {
    
        private Long id;

        private String name;

        private Integer count;

        private String unit;

        @ManyToOne(fetch = FetchType.LAZY)
        @JoinColumn(name = "RECIPES_ID", nullable = false)
        private Recipes recipes;
    }

I was asked to implement filtering service with these parameters:

1. Recipes type(Vegetarian, Dairy, Fast food)
2. Number of serving
3. Specific ingredients (either include or exclude)

So I created a FilterRequestDTO based on them and implemented my service as below:

@Override
public ListResponseDTO<RecipesDTO> getFilteredRecipes(FilterRecipesDTO filterRecipesDTO) {
    ListResponseDTO<RecipesDTO> response = new ListResponseDTO<>();
    var temp = repository.findFilteredRecipes(
            Objects.nonNull(filterRecipesDTO.getType()) ? RecipesType.parseRecipesType(filterRecipesDTO.getType()) : null,
            Objects.nonNull(filterRecipesDTO.getServeCount()) ? filterRecipesDTO.getServeCount() : null,
            filterRecipesDTO.getIncludeIngredients().size() > 0 ? filterRecipesDTO.getIncludeIngredients() : null,
            filterRecipesDTO.getExcludeIngredients().size() > 0 ? filterRecipesDTO.getExcludeIngredients() : null,
            PageRequest.of(filterRecipesDTO.getPage(), filterRecipesDTO.getSize()));

    if (temp.isPresent()) {
        response = new ListResponseDTO<>(filterRecipesDTO.getPage(), temp.get().getNumberOfElements());
        for (Recipes recipes : temp.get().getContent())
            response.getData().add(new RecipesDTO().from(recipes));
    }
    return response;
}

And it calls my Repository method layer which looks like:

@Query("select r from Recipes r join Ingredient i on r.id = i.recipes.id " +
        "where (:typ is null or r.type = :typ) " +
        "and (:cnt  is null or r.serveCount = :cnt) " +
        "and (:inc is null or i.name in :inc) " +
        "and (:exc is null or i.name not in :exc)")
Optional<Page<Recipes>> findFilteredRecipes(@Param("typ") RecipesType type,
                                            @Param("cnt") Integer serveCount,
                                            @Param("inc") List<String> includeIngredients,
                                            @Param("exc") List<String> excludeIngredients,
                                            Pageable page);

But when I want to filter results with given includeIngredients and excludeIngredients lists, it does not seem to work.

Do you have any idea how to solve this issue?

Any help would be appreciated!!

----------> UPDATE <----------

With help of @Andrey, finally I could change my repository method to :

@Query(value = "select r from Recipes r " +
            "where ((:typ) is null or r.type in :typ) " +
            "and ((:cnt)  is null or r.serveCount in :cnt) " +
            "and ((:inc) is null or exists (select 1 from Ingredient i where i.recipes = r and i.name in :inc)) " +
            "and ((:exc) is null or not exists (select 1 from Ingredient i where i.recipes = r and i.name in :exc)) ")
    Optional<Page<Recipes>> findFilteredRecipes(@Param("typ") List<RecipesType> types,
                                                @Param("cnt") List<Integer> serveCounts,
                                                @Param("inc") List<String> includeIngredients,
                                                @Param("exc") List<String> excludeIngredients,
                                                Pageable page);

As you can see I have also changed dataType of Type and ServeCount search parameters to list<> to extend the ability to filter my data.

Solution

If you need to return receipt if it contains at least one ingredient requested, the query would be:

select r from Recipes r
where (:typ is null or r.type = :typ)
and (:cnt  is null or r.serveCount = :cnt)
and ((:inc) is null or exists (select 1 from Ingredient i where i.recipes=r and i.name in :inc)) 
and ((:exc) is null or not exists (select 1 from Ingredient i where i.recipes=r and i.name in :exc))

if the requirement is to return receipt with all ingredients requested, the query would be:

select r from Recipes r
where (:typ is null or r.type = :typ)
and (:cnt  is null or r.serveCount = :cnt)
and ((:inc) is null or :inccnt = (select count(1) from Ingredient i where i.recipes=r and i.name in :inc)) 
and ((:exc) is null or not exists (select 1 from Ingredient i i.recipes=r and where i.name in :exc))

Answered By - Andrey B. Panfilov
Answer Checked By - Robin (JavaFixing Admin)