Ecto query many to many associations that match all elements of a list

Questions / Help
, , ,
1

Hi. In my application I have a many to many association between Product and Tag schemas which goes through the ProductTag table. Quite straight forward, however, I need to list all products that have ALL the tags in a given list. I really cannot find any answer as to how one can do this. This is what I have so far:

def list_category_products(categories) do
        from(
            p in Product,
            join: pt in ProductTag, on: pt.product_id == p.id,
            join: t in Tag, on: pt.tag_id == t.id,
            where: t.name in ^categories
        )
        |> Repo.all()
    end

Lets assume I have product1 associated with the tag “cake” and product2 associated with the tag “tea”. If I pass only “cake” or only “tea” to the function, I get the correct product, but if I pass both “cake” and “tea”, I get both products where I expect to receive none as none of my products have both tags.

I will appreciate any help or advice. Thank you all in advance.

2

Hi David,
List of product ids can be retrieved using this query (I didn’t test):

from tags in Tag,
  join: products_tags in ProductTag, on: products_tags.tag_id == tags.id,
  where: tags.name in ^categories,
  select: products_tags.product_id,
  group_by: products_tags.product_id,
  having: count() == ^length(categories)

After that you can get list of products using subquery or just separate query

1 Like
3

Thank you very much. I will try it now.