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)
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.