Using preload data in a order_by

I think you have to use the join syntax to do this since preloads are not bound.

Try seeing if this gives you the desired result:

from cs in ClientSeance, 
  preload: [:seance], 
  join: s in Seance, 
  on: s.id == cs.seance_id,
  order_by: s.date 

Hi voughtdq,

Thank you for the answer !
I managed to order my ClientSeance using the date field of seance.

My next step is to group_by Client.id and sum on cs.nbrError.
Do you know what is the problem here ?
It’s weird because I used cs in the select …

query = from cs in ClientSeance,
preload: [:seance],
join: s in Seance,
on: s.id == cs.seance_id,
join: c in Client,
on: c.id == cs.client_id,
order_by: [desc: s.date],
select: %{id: c.id, nbrError: sum(cs.nbrError), date: s.date}

** (Ecto.QueryError) the binding used in `from` must be selected in `select` when using `preload` in query:

from c0 in ExamenDuCode.ClientSeance,
  join: s in ExamenDuCode.Seance,
  on: s.id == c0.seance_id,
  join: c1 in ExamenDuCode.Client,
  on: c1.id == c0.client_id,
  order_by: [desc: s.date],
  select: %{id: c1.id, nbrError: sum(c0.nbrError)},
  preload: [:seance]

    (ecto) lib/ecto/repo/queryable.ex:124: Ecto.Repo.Queryable.execute/5
    (ecto) lib/ecto/repo/queryable.ex:37: Ecto.Repo.Queryable.all/4

When a query includes preload: [:seance], Ecto will actually run the main query first, then fetch the association mentioned in preload in a separate query. This is why it wasn’t possible to order by cs.seance.date in your original post: seance wasn’t loaded at the time.

In your last query, even though you added join: s in Seance, preload: [:seance] would still run as a separate query after the first one. Ecto logs every SQL statement it sends to the DB with the debug level, I suggest you study those logs to see how Ecto transforms queries into SQL.

You probably want to use preload: [seance: s] instead of preload: [:seance] to have Ecto use the joined table to load the seance association instead of performing a second query. As for the error you’re getting

** (Ecto.QueryError) the binding used in `from` must be selected in `select` when using `preload` in query

here’s what it means: If you don’t select the complete s struct, Ecto won’t be able to put the preloaded association on it.

If you all you need from s is its date, remove the preload: [:seance] line since you’re already joining on the Seance schema.