Preload with Ecto with subquery and using a join from the main query

Question

I'm using Ecto to request data from the database, and I've used the following code to preload the cats filtered by price_discount.

Item
|> join(:inner, [i], c in Cat, c.food_category_id == i.id)
|> where([i, c], is_nil(c.price_discount))
|> preload([i, c],
  [
    cats: c,
    cats: :houses
  ])

This works great, but now I want to order Houses by id, so I was hopping this work:

Item
|> join(:inner, [i], c in Cat, c.food_category_id == i.id)
|> where([i, c], is_nil(c.price_discount))
|> preload([i, c],
  [
    cats: c,
    cats: [
      houses: from(h in Houses, order_by: h.id)
    ]
  ])

But it doesn't, this is the error:

from(h in Houses, order_by: h.id()) is not a valid preload expression. preload expects an atom, a list of atoms or a keyword list with more preloads as values. Use ^ on the outermost preload to interpolate a value

The only post to work is to just use one of the following:

• Use cats: c without the order
• Use houses: from(h in Houses, order_by: h.id)

The following works, using ^:

Item
|> join(:inner, [i], c in Cat, c.food_category_id == i.id)
|> where([i, c], is_nil(c.price_discount))
|> preload([i, c],
  ^[
    cats: [
      houses: from(h in Houses, order_by: h.id)
    ]
  ])

Is this a bug in Ecto ?

Gastrology

Can you try cats: {c, houses: from(h in Houses, order_by: h.id)}? – Fultz

I don't think that will work. The solution that I found is to use to |> preload(), one with [cats: c] and another with [cats: [houses: from( ... )]]. – Trapan

Can you try it out once? I think what you're doing is described in the documentation of preload under "Nested associations can also be preloaded in both formats". See hexdocs.pm/ecto/Ecto.Query.html#preload/3. – Fultz

@Dogbert, thanks it worked. cats: {c, houses: ^from(h in Houses, order_by: h.id)} is the correct format. I didn't know how to use {} in preload, now I understand better. – Trapan

Answer 1

When you want to provide a custom query to a preload and also to its nested preloads, you can use the tuple syntax, where the first element is the main query and the second element is a list of preloads just like you'd have at the top level.

What you want can be achieved with this:

|> preload([i, c], [cats: {c, houses: ^from(h in Houses, order_by: h.id)}])

Fultz

Answer 2

The solution that I found was to split |> preload() in two.

Item
|> join(:inner, [i], c in Cat, c.food_category_id == i.id)
|> where([i, c], is_nil(c.price_discount))
|> preload([i, c], [cats: c])
|> preload([cats: [houses: from(h in Houses, order_by: h.id)]])

Gastrology