Help with ecto query

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

defmodule OrderProduct do

  schema "order_product" do
   belongs_to :product, Product

defmodule Product do
   schema "product" do
   many_to_many :categories, Category, join_through: "product_category"

defmodule Category do
  schema "category" do
  many_to_many: :brochures, Brochure, join_through: "brochure_category"

defmodule Brochure do
  schema "brochure" do
  many_to_many :categories, Category, join_through: "brochure_category"

Relevant tables are:

(join table) product_category
(join table) brochure_category

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: == 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 == 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:

   products: [...],
   brochures: [...]


[ {%Order{}, [%Brochure{}, %Brochure{},...]} ]
1 Like

Why? Maybe I’m misunderstanding here.

Why can’t you do a query grabbing just one order and listing out all broschures? You can run it multiple time and just have an list of query results for each orders?

Oh I get it you want multiple select in one query.

Have you try looking into select_merge/3 Ecto.Query — Ecto v3.11.1 ? Nope that doesn’t work either. Sorry no clue, best of luck.

Apart from the or b.permanent == true clause in that query, this could be solved with :through associations and preload - the post-processing you describe is equivalent to how preloading works.

1 Like

Did you arrive at a solution? I’d try to help if you made a small GH repo.