Ah. You can tweak what you have a little I think and do:

Alarms
|> join(:left, [a], e in assoc(a, :event)) 
|> order_by([a, e], [{:desc, e.id}]) 
|> preload([a, e], event: e)
|> Repo.all()

In any case, doing the join so that you can order seems like exactly the right way to do the SQL.

Order on each respective table to get a total order:

   albums_query =
      Album
      |> order_by([m], [desc: m.id])

    Artist
    |> join(:left, [a], m in assoc(a, :albums))
    |> order_by([a,m], [asc: a.id])
    |> preload([a,m], [albums: ^albums_query])
    |> Repo.all()

That being said associations by their nature have to stay clustered together.

Order gets overridden:

|> preload([a,m], :albums)

Order is respected

|> preload([a,m], [albums: m])

At this point I’m not even sure what the second one means … I blame macros as the difference seems a bit subtle (and as it should be unrelated).

Ok - preload/3:

• [:albums] give me the whole bag unconstrained by any SQL constraints.
• [albums: m] just give me those items in the bag that are in compliance with the SQL constraints.

The optional/vanishing list markers aren’t helping clarity either.

Check out preload queries, they’re perfect for this: https://hexdocs.pm/ecto/Ecto.Query.html#preload/3-preload-queries

Not too elegant…
Unsure how I can generalise this, and reuse it…

It’s not exactly clear what kind of “reuse” (or elegance) you are after.