Preload with order

From Ecto.Query - Preload quries

comments_query = from c in Comment, order_by: c.published_at
Repo.all from p in Post, preload: [comments: ^comments_query]

I need to preload many assoc of one record so it is now like this

Post
|> preload(comments: ^from(c in Comment, order_by c.published_at)
# more preload
|> Repo.get(id)

Can I make this simpler? For example, Post already knows :comments assoc is for Comment, so I want to remove that part. Using wrong schema in from inside preload makes runtime error, by the way.

Post
|> preload(comments: ^from(c in OtherSchema, order_by c.other_field)
# more preload
|> Repo.get(id)

You can reflect over the schema to build a query for the association:

defmodule EctoHelpers do
  def preload_assoc(queryable, assoc) do
    schema = queryable.from |> elem(1)
    assoc_schema = schema.__schema__(:association, assoc).related
    preload_query = from(assoc_schema, order_by: [desc: :id])
    preloads = [{assoc, preload_query}]
    preload(queryable, ^preloads)
  end
end

Then use it like:

iex(23)> from(Blog.Article) |> EctoHelpers.preload_assoc(:comments)
#Ecto.Query<from a in Blog.Article,
 preload: [[comments: #Ecto.Query<from c in Blog.Comment, order_by: [desc: c.id]>]]>
1 Like

This generates an error:

cannot use ^from(...) outside of match clauses

Likewise, the EctoHelpers solution generates a similar error:

cannot use ^preloads outside of match clauses

Need to import Ecto.Query for access to from etc from Ecto…

Thanks… it’s closer, but now it’s a runtime error (instead of a compile-time error):

** (UndefinedFunctionError) function nil.related/0 is undefined or private

Sorry to be such a noob… once I get this figured out and stare at it for a while it’ll probably make sense, but for now I’m just bumbling through this unfamiliar stuff.

GOT IT. Here’s the syntax for ordering related schemas recursively (2 levels deep in this case):

Repo.get_by!(Menuitem, slug: slug, parent_id: 0)
    |> Repo.preload([{:children, from(m in Menuitem, order_by: m.order)}, children: [children: from(m in Menuitem, order_by: m.order)]])

That looks crazy weird, but the best sense I can make of it is that you sorta have to reference the 1st association (children) twice: once as a tuple so you can tell it how to sort its relations, then secondly as a regular string so you can continue down the wormhole to tell it to include its own relations (the children’s children in this case).

3 Likes

In 2021, this solution works and easy to grok for a newbie like me https://elixirforum.com/t/ecto-query-order-for-preload/16714

2 Likes

I am from the future also, ; ) and here is another approach that works in 2022:

You can specify your preferred preload_order in your schema, as a parameter to has_many() or many_to_many().

Here is an example (notice preload_order passed to has_many() ):


defmodule Journey.Schema.Execution do
   ...
  schema "executions" do
    ...
    has_many(:computations, Journey.Schema.Computation, preload_order: [desc: :ex_revision])
    ...
  end
   ...
end
defmodule Journey.Schema.Computation do
  ...
  schema "computations" do
    belongs_to(:execution, Journey.Schema.Execution)
    ...
    field(:ex_revision, :integer, default: 0)
    ...
  end
  ...
end

Your actual query can be quite basic:

  Journey.Repo.get(Journey.Schema.Execution, id)
  |> Journey.Repo.preload(:computations)

The approach is documented here: Ecto.Schema — Ecto v3.9.1

AFAICT, this feature was merged in early 2021 (not sure of the Ecto version): Add preload_order in associations by thiamsantos · Pull Request #3524 · elixir-ecto/ecto · GitHub

1 Like