Hi folks, I am very new to Elixir and Ecto and struggling to express an SQL query in Ecto where I have to select computed values from a CTE into virtual fields.
Schema and query look like this (simplified for readability):
schema "readings" do
field :read_at, :naive_datetime
field :value, :integer
field :previous_value, :integer, virtual: true
field :current_value, :integer, virtual: true
end
current_values_query =
Reading
|> select_merge([r], %{
current_value:
coalesce(
r.value,
lag(r.value)
|> over(order_by: r.read_at)
),
})
query = { "current_values", Reading }
|> with_cte("current_values", as: ^current_values_query)
|> select_merge([r], %{
previous_value:
lag(r.value)
|> over(order_by: r.read_at),
})
|> order_by([r], [asc: r.read_at])
In a nutshell, I am querying a timeseries of current and previous values, and there might be gaps in values, in which which case the effective current value (current_velue
) is the value from the previous row.
Because PostgreSQL does not allow values from window functions within window functions, I have to resort to a CTE.
The above query compiles and executs fine, but current_value
is always nil
.
The generated SQL looks like this, which seems correct, except missing c0.current_value
in the second SELECT
:
WITH "current_values"
AS (SELECT sr0."id" AS "id",
sr0."read_at" AS "read_at",
sr0."value" AS "value",
sr0."gas" AS "gas",
sr0."water" AS "water",
sr0."inserted_at" AS "inserted_at",
sr0."updated_at" AS "updated_at",
COALESCE(sr0."value",
LAG(sr0."value") OVER (ORDER BY sr0."read_at")) AS "current_value"
FROM "readings" AS sr0)
SELECT c0."id",
c0."read_at",
c0."value",
c0."gas",
c0."water",
c0."inserted_at",
c0."updated_at",
LAG(c0."value") OVER (ORDER BY c0."read_at")
FROM "current_values" AS c0
ORDER BY c0."read_at";
Am I doing it wrong? Is this a limitation? Is this a bug?