Order By Association Count within a Dynamically Built Query in Phoenix / Ecto

Phoenix Forum Questions / Help
, ,
2

Not sure if this will help in your particular situation but starting simply with this raw SQL:

SELECT COUNT(t.id), a.title FROM albums AS a
  JOIN tracks AS t ON a.id = t.album_id
  GROUP BY a.id
  ORDER BY COUNT(t.id) DESC;

which can be expressed in keywords form as

query = from(a in Album, [
  join: t in Track, on: a.id == t.album_id,
  group_by: a.id,
  select: [count(t.id), a.title],
  order_by: [desc: count(t.id)]
])

iex(1)> alias MusicDB.{Repo,Album,Track}
[MusicDB.Repo, MusicDB.Album, MusicDB.Track]
iex(2)> import Ecto.Query
Ecto.Query
iex(3)> query = from(a in Album, [
...(3)>   join: t in Track, on: a.id == t.album_id,
...(3)>   group_by: a.id,
...(3)>   select: [count(t.id), a.title],
...(3)>   order_by: [desc: count(t.id)]
...(3)> ])
#Ecto.Query<from a in MusicDB.Album, join: t in MusicDB.Track,
 on: a.id == t.album_id, group_by: [a.id], order_by: [desc: count(t.id)],
 select: [count(t.id), a.title]>
iex(4)> rows = Repo.all(query)

21:54:28.302 [debug] QUERY OK source="albums" db=2.4ms
SELECT count(t1."id"), a0."title" FROM "albums" AS a0 INNER JOIN "tracks" AS t1 ON a0."id" = t1."album_id" GROUP BY a0."id" ORDER BY count(t1."id") DESC []
[
  [10, "You Must Believe In Spring"],
  [9, "Portrait In Jazz"],
  [5, "Cookin' At The Plugged Nickel"],
  [5, "Kind Of Blue"],
  [4, "Live At Montreaux"]
]
iex(5)>

or in expression form as

from(a in Album)
|> join(:inner, [a], t in Track, a.id == t.album_id)
|> group_by([a,t], a.id)
|> select([a,t], [count(t.id), a.title])
|> order_by([a,t], desc: count(t.id))

iex(1)> alias MusicDB.{Repo,Album,Track}
[MusicDB.Repo, MusicDB.Album, MusicDB.Track]
iex(2)> import Ecto.Query
Ecto.Query
iex(3)> make_query =
...(3)>   fn ->
...(3)>     from(a in Album)
...(3)>     |> join(:inner, [a], t in Track, a.id == t.album_id)
...(3)>     |> group_by([a,t], a.id)
...(3)>     |> select([a,t], [count(t.id), a.title])
...(3)>     |> order_by([a,t], desc: count(t.id))
...(3)>   end
#Function<20.127694169/0 in :erl_eval.expr/5>
iex(4)> rows = Repo.all(make_query.())

21:56:14.978 [debug] QUERY OK source="albums" db=2.6ms
SELECT count(t1."id"), a0."title" FROM "albums" AS a0 INNER JOIN "tracks" AS t1 ON a0."id" = t1."album_id" GROUP BY a0."id" ORDER BY count(t1."id") DESC []
[
  [10, "You Must Believe In Spring"],
  [9, "Portrait In Jazz"],
  [5, "Cookin' At The Plugged Nickel"],
  [5, "Kind Of Blue"],
  [4, "Live At Montreaux"]
]
iex(5)>

Whether or not you can successfully compose such a query in your particular situation depends entirely on the details …

3 Likes