In some occasions, you might need to use alias in your Ecto
query. For example, while developing TIL, I need to use both date_trunc
(PostgreSQL) together with group_by
.
In SQL, it can be written as:
select date_trunc('day', inserted_at) as date
from "posts"
group by date;
In Ecto
, you can use fragment
to call database function. Hence, to use date_trunc
, we can just:
from q in Post,
select: [fragment("date_trunc('day', ?) as date", q.inserted_at],
group_by: :date
However, this will raise an error mentioning that date
column is not available for Post
table. Hence, we need a way to use the SQL aliases date
in the query.
To use SQL alias in a group_by
, we can simply use fragment
again.
from q in Post,
select: [fragment("date_trunc('day', ?) as date", q.inserted_at],
group_by: fragment("date")
I was able to solve this thanks to this Stack Overflow question.