Using preload data in a order_by

Questions / Help
1

Hi Everyone !
I have a question related to preload and using a field from the preload to order_by.

Here are the 3 schemas I’m working on. I’m new in Ecto so I might took bad decision on the model don’t hesitate to correct me :wink:

defmodule ExamenDuCode.Client do
  use Ecto.Schema
  alias ExamenDuCode.{Seance, Examen}

  schema "clients" do
    field(:lastname, :string)
    field(:firstname, :string)
    field(:address, :string)
    field(:birthdate, :date)
    timestamps()
    many_to_many(:seances, Seance, join_through: "clients_seances")
    many_to_many(:examens, Examen, join_through: "clients_examens")
  end
end

defmodule ExamenDuCode.Seance do
  use Ecto.Schema
  alias ExamenDuCode.{Serie}

  schema "seances" do
    field(:date, :date)
    many_to_many(:clients, ExamenDuCode.Client, join_through: "clients_seances")
    belongs_to(:serie, Serie)
    timestamps()
  end
end

defmodule ExamenDuCode.ClientSeance do
  alias ExamenDuCode.{Client, Seance}

  use Ecto.Schema

  @primary_key false
  schema "clients_seances" do
    belongs_to(:client, Client)
    belongs_to(:seance, Seance)

    field(:nbrError, :integer)
  end
end

Here are some information about the problem:
A customer Client wants a driving licence.
He can participate to all the sessions Seance he wants
Customer accumulate errors during the session nbrError
The customer is allowed to participate to the driving licence if he has less or equal to 5 errors during the last 4 sessions.

The hard part I think is to handle the nbrError in the association Schemaclients_seances

I tried this but I have this problem

Repo.all(from cs in ClientSeance, preload: :seance, order_by: cs.seance.date)

** (Ecto.Query.CompileError) `cs.seance().date()` is not a valid query expression
    (ecto) expanding macro: Ecto.Query.order_by/3
    priv/repo/seeds.exs:176: (file)
    (ecto) expanding macro: Ecto.Query.from/2
    priv/repo/seeds.exs:176: (file)
    (elixir) expanding macro: Kernel.|>/2
    priv/repo/seeds.exs:177: (file)

The preload data is not available to be used with the order_by.
Any ideas ?

2

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
3

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

1 Like