query =
from s in State,
group_by:
fragment("date_part(?, ?)", ^group, s.timestamp_start),
select:
{fragment("date_part(?, ?)", ^group, s.timestamp_start), count(s.id)}
Repo.all(query)
Where group might be one of [‘minute’, ‘hour’, ‘day’].
However, I get the following error:
ERROR 42803 (grouping_error) column "s0.timestamp_start" must appear in the GROUP BY clause or be used in an aggregate function
But if I add s.timestamp_start to group_by clause, I don’t get the expected result, since dates are grouped by timestamp_start and not by the part defined.
How can I do this without having to specify a query to each group possibility (passing the literal value inside fragment ) ?
My guess is that it’s generating distinct placeholders for the first and second ^group, so the query planner can’t see that the expression in SELECT and GROUP BY are always going to be the same.
If that’s happening: since there’s a very fixed set of possible values for group, one approach would be to interpolate it into the fragment using plain Elixir string interpolation. This is a Bad Thing to do for arbitrary input, but seems reasonable here.