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

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