How to use special clauses in Ecto

Questions / Help
1

Hey all,

we are doing a complex Postgres query using the WINDOW clause.

As this is very specific and only applies to Postgres I don’t assume this will be officially supported by Ecto. However I wonder if there is a way to specify custom clauses (similar to fragments).

Something along those lines could work:

from s in Salary, select: fragment("avg(salary) OVER w"), custom_clause: "WINDOW w AS (...)"

Resulting in:

SELECT avg(salary) OVER w FROM salaries WINDOW w AS (...)

Hope that makes sense.

3 Likes
2

I’ve just run into the same thing. Did you ever find a solution? (short of using raw SQL and Ecto.Adapters.SQL.query)

3

I ‘think’ fragments should still work fine for it. Do you have a precise example of existing working code and what you want it to do instead?

4

Yes you can repeat the partitioning clauses in each portion of the select with fragments. But it gets ‘ugly’ with many columns.

Here’s some SQL we’d like to build in Ecto:

select distinct
  case
    when (first_value(event_details ->> 'map_number') over w)::int % 2 = 0 then 'even'
    else 'odd'
  end,
  round(extract(epoch from first_value(created_at) over w))::int,
  (first_value(event_details ->> 'map_number') over w)::int,
  round(
    100.0 *
    (first_value(event_details ->> 'correct_count') over w)::numeric /
    nullif((first_value(event_details ->> 'total_count') over w)::int, 0)::numeric
  )::int
from the_table
window w as (partition by (event_details ->> 'map_number')::int order by created_at desc)

That was an experiment in improving the code from the existing solution of selecting rank() over … as rank in a sub-query, then selecting from that where rank = 1. This works perfectly fine, but we were playing around with first_value to simplify away the sub-select.

Being able to add that last line makes it simpler. Repeating the window function in each part of the select (via a fragment) works, but does not look make it look simpler.

1 Like
5

Ah, might need to wait for someone to look at that, not something that I’ve done yet myself, hmm…