Search code examples
sqlpostgresqlelixirecto

Ecto Fragment interpolating var in group_by

I am trying to the following:

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) ?

Solution

  • Found the answer here.

    You can use as in fragments to reference eachother:

        from(m in Message,,
      group_by: fragment("timestamp"),
      select: fragment("date_trunc(?, ?) as timestamp", ^timeseries, m.inserted_at)
    )