Filter by has_many relationship


Here is my models :

  schema "recipes" do
    has_many :parts, Hello.Part
  schema "parts" do
    has_many :part_ingredients, Hello.PartIngredient
    belongs_to :recipe, Hello.Recipe
  schema "part_ingredients" do
    belongs_to :part, Hello.Part
    belongs_to :ingredient, Hello.Ingredient

I would like to filter the recipes by the ingredients but only if the recipe have all the ingredients.
Something like that was working but it’s not scalable:

    query = from r in Recipe,
      join: p in assoc(r, :parts),
      join: pi1 in assoc(p, :part_ingredients),
      join: pi2 in assoc(p, :part_ingredients),
      where: pi1.ingredient_id == 1,
      where: pi2.ingredient_id == 9,

I can’t do in my queries a AND like find all recipes which have this ingredient AND this ingredient.

Do you have some suggestions ?

Thanks !

Unfortunately Ecto do not support existential queries which would be the perfect pick there. Instead we can use JOINs with subqueries like that:

from r in Recipe,
  join: p in assoc(r, :parts),
  join: pi in subquery(from pi in PartIngredient, where: pi.ingredient_id in [1, 9]),
  on: == pi.part_id
1 Like

Thanks for your answer !

I already tried that, but this is like a OR. This query will get a recipe with ingredients 1 OR 9.
I don’t know how to do that, i can’t for example use the i, the subquery, may be it could be a solution.

Any solution to do that ?

Have you thought about using raw sql? Like with this.