Compose your query dynamically:
iex(1)> alias MusicDB.{Album,Track}
[MusicDB.Album, MusicDB.Track]
iex(2)> import Ecto.Query
Ecto.Query
iex(3)> album_id = 2
2
iex(4)> query = from(a in Album, [
...(4)> join: t in Track, on: a.id == t.album_id,
...(4)> where: a.id == ^album_id,
...(4)> select: %{
...(4)> a: a.id,
...(4)> t: t.id
...(4)> }
...(4)> ])
#Ecto.Query<from a in MusicDB.Album, join: t in MusicDB.Track,
on: a.id == t.album_id, where: a.id == ^2, select: %{a: a.id, t: t.id}>
iex(5)> query1 = order_by(query, [a,t], asc: a.id, desc: t.id)
#Ecto.Query<from a in MusicDB.Album, join: t in MusicDB.Track,
on: a.id == t.album_id, where: a.id == ^2, order_by: [asc: a.id, desc: t.id],
select: %{a: a.id, t: t.id}>
iex(6)> query2 = order_by(query, [a,t], asc: t.id)
#Ecto.Query<from a in MusicDB.Album, join: t in MusicDB.Track,
on: a.id == t.album_id, where: a.id == ^2, order_by: [asc: t.id],
select: %{a: a.id, t: t.id}>
iex(7)> Repo.all(query1)
12:52:16.623 [debug] QUERY OK source="albums" db=2.2ms
SELECT a0."id", t1."id" FROM "albums" AS a0 INNER JOIN "tracks" AS t1 ON a0."id" = t1."album_id" WHERE (a0."id" = $1) ORDER BY a0."id", t1."id" DESC [2]
[%{a: 2, t: 10}, %{a: 2, t: 9}, %{a: 2, t: 8}, %{a: 2, t: 7}, %{a: 2, t: 6}]
iex(8)> Repo.all(query2)
12:52:18.568 [debug] QUERY OK source="albums" db=3.3ms
SELECT a0."id", t1."id" FROM "albums" AS a0 INNER JOIN "tracks" AS t1 ON a0."id" = t1."album_id" WHERE (a0."id" = $1) ORDER BY t1."id" [2]
[%{a: 2, t: 6}, %{a: 2, t: 7}, %{a: 2, t: 8}, %{a: 2, t: 9}, %{a: 2, t: 10}]
iex(9)>
