How to build sorted group_by queries with ecto?

Questions / Help
1

I have an events table with starts_at column of datetime type.
And now I’d like to output a calendar with date cells showing count of events per day.
So, I need to build a query like this:

SELECT count(id), char(starts_at, "YYYY-mm-dd") AS date FROM events GROUP BY date ORDER BY date ASC

I have tried to build it like this:

def events_count_grouped_by_date() do
  Event
  |> group_by([e], fragment("to_char(?, ?)", e.starts_at, "YYYY-mm-dd"))
  |> select([e], %{date: fragment("to_char(?, ?)", e.starts_at, "YYYY-mm-dd"), count: count(e.id)})
  |> order_by([e], asc: e.starts_at)
end

But this query gives postgres error:

[debug] QUERY ERROR source="events" db=0.0ms
SELECT to_char(e0."starts_at", 'YYYY-mm-dd'), count(e0."id") FROM "events" AS e0 GROUP BY to_char(e0."starts_at", 'YYYY-mm-dd') ORDER BY e0."starts_at" []
** (Postgrex.Error) ERROR 42803 (grouping_error) column "e0.starts_at" must appear in the GROUP BY clause or be used in an aggregate function

If I remove the last line in the function like this:

def events_count_grouped_by_date() do
  Event
  |> group_by([e], fragment("to_char(?, ?)", e.starts_at, "YYYY-mm-dd"))
  |> select([e], %{date: fragment("to_char(?, ?)", e.starts_at, "YYYY-mm-dd"), count: count(e.id)})
end

In this case the query works but the results aren’t sorted.

Then I tried to build something using subquery:

def events_count_grouped_by_date() do
  aggs = Event
  |> group_by([e], fragment("to_char(?, ?)", e.starts_at, "YYYY-mm-dd"))
  |> select([e], %{id: e.id, date: fragment("to_char(?, ?)", e.starts_at, "YYYY-mm-dd"), count: count(e.id)})
  Event
  |> join(:inner, [e], a in subquery(aggs), on: e.id == a.id)
  |> select([e, a], {e.id, e.starts_at, a.date, a.count})
end

And got error like the first one:

[debug] QUERY ERROR source="events" db=0.0ms
SELECT e0."id", e0."starts_at", s1."date", s1."count" FROM "events" AS e0 INNER JOIN (SELECT e0."id" AS "id", to_char(e0."starts_at", 'YYYY-mm-dd') AS "date", count(e0."id") AS "count" FROM "events" AS e0 GROUP BY to_char(e0."starts_at", 'YYYY-mm-dd')) AS s1 ON e0."id" = s1."id" WHERE (e0."starts_at" > $1) AND (e0."starts_at" < $2) [#DateTime<2019-01-23 00:00:00Z>, #DateTime<2019-02-24 00:00:00Z>]
** (Postgrex.Error) ERROR 42803 (grouping_error) column "e0.id" must appear in the GROUP BY clause or be used in an aggregate function

How to build such query with ecto?

2

All columns in GROUP BY need to be either:

  • aggregated
  • present as GROUP BY expressions

In your query id is neither.

3

Yes, I know, but if I remove it from query like this:

def events_count_grouped_by_date() do
  aggs = Event
  |> group_by([e], fragment("to_char(?, ?)", e.starts_at, "YYYY-mm-dd"))
  |> select([e], %{date: fragment("to_char(?, ?)", e.starts_at, "YYYY-mm-dd"), count: count(e.id)})
  Event
  |> join(:inner, [e], a in subquery(aggs), on: e.id == a.id)
  |> select([e, a], {e.id, e.starts_at, a.date, a.count})
end

postgres gives the error unknown column e.id.

4

Because there is no id column in aggs.

1 Like
5

Ok, I reworked it like this:

  def events_count_grouped_by_date() do
    Event
    |> group_by([e], fragment("date(?)", e.starts_at))
    |> select([e], %{date: fragment("date(?)", e.starts_at), count: count(e.id)})
    |> order_by([e], asc: fragment("date(?)", e.starts_at))
  end

And it finally works. Thanks for your help, @hauleth!

4 Likes