Ecto Fragment interpolating var in group_by and select

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

2 Likes

Can you post the generated SQL?

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.

Notably, fragment will not let you do this. I seem to recall there being something like unsafe_fragment but I’m not seeing any docs for it…

I agree that seeing the emitted SQL would be helpful.

@benwilson512 @al2o3cr here is the generated SQL.

SELECT date_part($1, s0."timestamp_start") 
FROM "states" AS s0 
GROUP BY date_part($2, s0."timestamp_start") ["month", "month"]

Var for group_by is different than the one on select.

bump! since I desperately need the answer to the same question :s

edit: after hours found a solution:

    from(m in Message,
      where:
        m.user == ^user
      group_by: [
        fragment("timestamp")
      ],
      select: {
        fragment("date_trunc(?, ?) as timestamp", ^timeseries, m.inserted_at),
        fragment("array_agg(?)", m.body)
      }
    )
6 Likes