I am struggling a bit coming up with a good query and looking for advice.
This is for a legacy ecommerce application with an addon written in ecto/phoenix.
The problem to solve:
- I need to select multiple orders at once
- Each order ships with a number of brochures.
- Which brochures depends on the products in the order.
- There are also some permanent brochures which goes out with every order.
The associations are nested and look something like this (Order has_many OrderProduct has_one Product has_many Category has_many Brochure)
defmodule Order do
schema "order" do
...
has_many :products, OrderProducts
end
defmodule OrderProduct do
schema "order_product" do
...
belongs_to :product, Product
end
end
defmodule Product do
schema "product" do
...
many_to_many :categories, Category, join_through: "product_category"
end
defmodule Category do
schema "category" do
...
many_to_many: :brochures, Brochure, join_through: "brochure_category"
end
defmodule Brochure do
schema "brochure" do
...
many_to_many :categories, Category, join_through: "brochure_category"
end
Relevant tables are:
order
order_product
product
(join table) product_category
category
(join table) brochure_category
brochure
The end result I would like to have is a brochure field on the Order schema with the brochures for the particular order.
To get the brochures per order I can have something like:
q = from b in Brochure,
join: bc in "brochure_category",
on: b.id == bc.brochure_id or b.permanent == true,
join: pc in "product_category",
on: bc.category_id == pc.category_id,
join: op in "order_product",
on: op.product_id == pc.product_id,
select: b,
where: op.order_id == ^order_id
This sort of works but how would I do this when selecting many orders at a time?
I can load everything into one structure like this:
q = from o in Order,
join: op in assoc(o, :products),
join: p in assoc(op, :product),
join: c in assoc(p, :categories),
join: bc in BrochureCategory,
on: bc.category_id == c.category_id,
join: b in Brochure,
on: bc.brochure_id == b.id or b.permanent == true,
preload: [products: {op, [product: {p, [categories: {c, [brochures: b]}]}]}]
orders = Repo.all(q)
The problem with this is that the Brochures are nested on each Category and duplicated. And I have to post-process things. It is workable but ugly and doesn’t feel right.
I’d be very happy if it is possible to construct a query which takes a number of order ids and returns the a list of Orders in the form:
[%Order{
products: [...],
brochures: [...]
},...]
or
[ {%Order{}, [%Brochure{}, %Brochure{},...]} ]
