Ecto: avoid to have to use a fragment twice (in select and group_by)

Questions / Help
1

Still trying to know my way around in ecto…

While this is working and delivering the proper result:

Authorization
|> where([m], m.status == :success)
|> where([m], fragment("date_part('year', inserted_at::date) = ?", 2023))
|> group_by(fragment("date_part('week', inserted_at::date)::INT"))
|> group_by([m], m.selection)
|> select([m], {fragment("date_part('week', inserted_at::date)::INT"), m.selection, count(m.id)})
|> Repo.all()

How can I avoid to use fragment("date_part('week', inserted_at::date)::INT") twice?

Appreciate a hint.

1 Like
2

selected_as should help
https://hexdocs.pm/ecto/Ecto.Query.API.html#selected_as/2

4 Likes
3

Thank you so much!

I was already looking at CTEs and scrolled passed selected_as

So the final solution is (for now :slightly_smiling_face:):

Authorization
|> where([m], m.status == :success)
|> where([m], fragment("date_part('year', inserted_at::date) = ?", 2023))
|> group_by(selected_as(:week))
|> group_by([m], m.selection)
|> select(
  [m],
  {selected_as(fragment("date_part('week', inserted_at::date)::INT"), :week), m.selection,
   count(m.id)}
)
|> Repo.all()
1 Like
4

FWIW, even though it feels like “repeating yourself” Postgres will only calculate that fragment value once even if it’s used in both SELECT and GROUP BY - it’ll even fail if the expression isn’t identical.

For instance, see this discussion where the same column was interpolated as two separate placeholders and angered the query planner:

1 Like
5

Thank you for pointing me at that discussion. This helped to increase my understanding how ecto is working