`or_where` query on has_many through relationships

Questions / Help
,
1

I’m having some trouble getting the result just right. This is my schema

schema "rooms" do
  # ...
  has_many :offerings, MyApp.Offering
  has_many :products, through: [:offerings, :product]
end

schema "products" do
  # ...
  belongs_to :product_category, MyApp.ProductCategory
  has_many :offerings, MyApp.Offering
  has_many :rooms, through: [:offerings, :room]
end

schema "product_categories" do
  has_many :products, MyApp.Product
end

schema "offerings" do
  belongs_to :product, MyApp.Product
  belongs_to :room, MyApp.Room
end

I want to only fetch rooms that contains certain products within a category. My first try:

query
|> join(:left, [room], products in assoc(room, :products))
|> preload([r, p], [:products, products: p])
|> where([r, p], p.product_category_id in ^product_categories)

Where product_categoires is a list of ids and query is a Ecto.Query being passed down, nothing special in that, just where statements on Room . The query works, but it will fetch rooms that have products with category 1, but not 3, when asked about [1,3], which makes sense when looking at the SQL generated. My next try:

new_query = query
    |> join(:left, [room], products in assoc(room, :products))
    |> preload([r, p], [:products, products: p])

Enum.reduce(product_categories, new_query, fn (id, acc) ->
  acc |> or_where([_r, p], p.product_category_id == ^id)
end)

Which didn’t work at all, pulling out duplicate rooms and just about everything else.

Any help appreciated.

2

So … I would probably use subqueries and joins using those subqueries to do this. I would turn this:

where([r, p], p.product_category_id in ^product_categories)

into a query of its own, then use that query in a join and perhaps add a where to limit the results from the join.

Also, for duplicate entries you can use distinct/3 which will eliminate duplicates based on your expression in the db engine.

3

Thanks for the reply!

Could you elaborate how you would use the subqueries? I gave this a spin, and didn’t really know how to proceed.

 product_query = 
   from r in Room, 
   join: p in assoc(r, :products), 
   where: p.product_category_id in ^product_categories

query
|> join(:left, [room], products in subquery(product_query))