Search code examples
mysqlsqlspring-bootjpamany-to-many

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:

  • Recipes (id, name)
  • Recipes_Ingredients(id, fk_recipe, fk_ingredient, quantity)
  • Ingredients (id, name)

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!

Solution 
  • 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.