Ecto query - order by column label

Hello, I have a simple query that groups rows and orders them by their count:

SELECT type, message, count(*) AS how_many FROM issues
GROUP BY type, message
ORDER BY how_many DESC

I’m unable to do the ordering with Ecto, as it looks that I cannot refer to the column label how_many. This is what I have so far:

from(i in Issue,
  select: %{ type: i.type, message: i.message, how_many: count("*") },
  group_by: [i.type, i.message]) |> Repo.all

This (without the ordering) works, but the SQL it generates does not include column labels:

SELECT i0."type", i0."message", count('*') FROM "issues" AS i0 GROUP BY i0."type", i0."message"

How should I write this in Ecto?

Thanks!

1 Like

Right now I believe you need to do the count("*") in the :order_by bit too. Even if you end-up doing it in both places, last time I checked the DB query engine optimized that away, so you should be fine.

2 Likes

Should definitely not be an issue… Many databases won’t even let you use ‘AS’ aliases in grouping and ordering, so the “standard” way to do it would indeed be to repeat the column expression again - COUNT(*), TO_CHAR(inserted_at, 'YYYY-MM-DD HH24:00:00), or whatever it might be.

Thank you! This worked fine:

from(i in Issue,
  select: %{ type: i.type, message: i.message, how_many: count("*") },
  group_by: [i.type, i.message],
  order_by: [desc: count("*")]) |> Repo.all

And produced this SQL:

SELECT i0."type", i0."message", count('*') FROM "issues" AS i0
GROUP BY i0."type", i0."message"
ORDER BY count('*') DESC
1 Like