How to build sorted group_by queries with ecto?

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?

All columns in GROUP BY need to be either:

  • aggregated
  • present as GROUP BY expressions

In your query id is neither.

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.

Because there is no id column in aggs.

1 Like

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!

3 Likes