Hi all,
Could anyone give me a hint on why is this query not returning the result I expect?
I’m following this blog post on how to use PostgreSQL with Ecto.
I have the following function in a Car
module :
def adjacent_prices do
query = """
WITH base AS (
SELECT lag(price, 2) OVER (ORDER BY price) lag,
lead(price, 2) OVER (ORDER BY price) lead,
c.*
FROM cars c
)
SELECT base.* FROM base
JOIN(
SELECT lag, lead
FROM base WHERE price = 5000
) sub ON base.price BETWEEN sub.lag AND sub.lead
"""
Repo.query!(query)
end
When I call Car.adjacent_prices()
, I expect the following five rows:
- one row with the car costing 5000
- two rows with the first two cars cheaper than 5000
- two rows with the first two cars more expensive than 5000
However, when I run the query, I get this result back:
iex(4)> Car.adjacent_prices()
[debug] QUERY OK db=1.3ms queue=1.4ms idle=1776.4ms
WITH base AS (
SELECT lag(price, 2) OVER (ORDER BY price) lag,
lead(price, 2) OVER (ORDER BY price) lead,
c.*
FROM cars c
)
SELECT base.* FROM base
JOIN(
SELECT lag, lead
FROM base WHERE price = 5000
) sub ON base.price BETWEEN sub.lag AND sub.lead
[]
%Postgrex.Result{
columns: ["lag", "lead", "id", "engine_type", "mpg", "kwh", "price", "make",
"model", "vin", "inserted_at", "updated_at"],
command: :select,
connection_id: 19391,
messages: [],
num_rows: 0,
rows: []
}
I have this data in the database:
And I’m using PostgreSQL 12.4 (Ubuntu 12.4-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
.
I also tried to run the query in PGadmin4, but it also did not return anything as a result.
It seems that I am missing something, but I can’t point out what exactly, even after reading the documentation on Ecto.Adapter.SQL.query/4