CODEWITHSUNDEEP
mysqlsqlspring-bootjpamany-to-many
Alessandro Passamonte
Alessandro Passamonte

Reputation: 27

How to find an entity containing a list of id (of the other entity) in a many-to-many relation on MySql/Jpa?

I have 3 tables:

I need to find all the recipes that containing a provided list of ingredients.

Es:

I provide a list of ingredients id like 111 (salt), 222(pepper), 333(oil) and I need to find all the recipes that containing these ingredients. The query should return me all those recipes that contain these ingredients.

I would also need a way to report the query back to Jpa.

Thanks in advance!

Upvotes: 1

Views: 382

Answers (2)

Iris
Iris

Reputation: 3

sql:

select distinct r.fk_recipe
from Recipes_Ingredients r
where r.fk_ingredient in (111,222,333)

In springboot jpa, you can use @Query

    @Query("select distinct r.fk_recipe from Recipes_Ingredients r 
            where r.fk_ingredient in :ingredients")
    List<Long> findRecipe(@Param("ingredients") Set<Long> ingredients);

And when you call the findRecipe method, create a Set as the param.

Upvotes: 0

Akina
Akina

Reputation: 42728

SELECT fk_recipe
FROM Recipes_Ingredients
WHERE fk_ingredient IN (111,222,333)
GROUP BY fk_recipe
HAVING COUNT(DISTINCT fk_ingredient) = 3

Join Recipes and Ingredients if needed.

Upvotes: 1

Related Questions