Ecto order_by in preload

Question

So I'm having trouble using order_by in my query with preload function.

Usually I use order_by like in this list_member function

list_member

def list_members() do
query =
  from(
    p in Member,
    select: p,
    order_by: [desc: p.inserted_at], # descending order
    preload: [:avatar]
  )

Repo.all(query)
end

But Here in my get_member_2 function I don't know where to put the order_by

def get_member_2!(id) do
query =
  from(
    p in Member,
    where: p.id == ^id,
    select: p,
    preload: [:avatar],
    preload: [:activities] # How to order_by in here
  )

Repo.one!(query)
end

Yardmaster

Answer 1

you have to create a subquery and order there

def get_member_2!(id) do
  query =
    from(
      p in Member,
      where: p.id == ^id,
      select: p,
      preload: [
        :avatar,
        activities:
          ^from(
            a in Activity,
            order_by: [desc: a.inserted_at]
          )
      ]
    )

  Repo.one!(query)
end

The matching of member and activity is done automatically

Truly

Perfect answer! Just to align the naming, it is not a subquery in the SQL sense but a customization of the preload query. – Meander

Is there any reason for Ecto to do order by parent id on top of the selected child field in such case? Or simply nobody cared to refactor/optimise this? – Litmus

Because if you order by parent id, when you preload several parents, once you encounter a new parent id you can consider it done and move to collecting for a new parent. – Truly