Filter by has_many relationship

Phoenix Forum Questions / Help
,
1

Hi,

Here is my models :

  schema "recipes" do
    has_many :parts, Hello.Part
    ...
    timestamps()
  end

  schema "parts" do
    has_many :part_ingredients, Hello.PartIngredient
    belongs_to :recipe, Hello.Recipe
    ...
    timestamps()
  end

  schema "part_ingredients" do
    belongs_to :part, Hello.Part
    belongs_to :ingredient, Hello.Ingredient
    ...
    timestamps()
  end

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,
      select: r.id

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 !

2

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: p.id == pi.part_id
1 Like
3

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 recipe.id i, the subquery, may be it could be a solution.

4

Any solution to do that ?

5

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