Proposal: Alias for current query select in Ecto queries

Chat / Discussions
, ,
1

Currently such query:

from foo in Foo,
 group_by: fragment("date_part(?, ?)", "day", foo.inserted_at),
 select: %{for: fragment("date_part(?, ?)", "day", foo.inserted_at), count: count(foo.id)}

Is invalid (at least in PostgreSQL) with very confusing error (at least for the newcomers):

column "p0.inserted_at" must appear in the GROUP BY clause or be used in aggregate function

Everything is because what DB sees is something like:

SELECT
  date_part(foo.inserted_at, $1) AS for,
  COUNT(foo.id) AS count
FROM foos foo
GROUP BY
    date_part(foo.inserted_at, $2)

And while we knows that value of $1 and $2 is the same the query planner cannot assume that, because there is not enough data. What we can do currently is:

from foo in Foo,
 group_by: fragment(~s["for"]),
 select: %{for: fragment("date_part(?, ?)", "day", foo.inserted_at), count: count(foo.id)}

Which will use our alias for for field, however this is sub perfect experience, what I would like to see instead is something like:

from foo in Foo,
 group_by: self.for,
 select: %{for: fragment("date_part(?, ?)", "day", foo.inserted_at), count: count(foo.id)}

Or similar (no need to use exactly self word). This would allow to have better experience when working with SQL functions and to reduce duplication in some OLAP queries.

Not that we cannot use foo as the foo.for as we can have the same field names in select as there are in foo and we need to distinguish between them.

1 Like