@Schultzer I’m not sure if I’m missing something, but I’m trying to use the ~SQL sigil with Ecto repo query function and i’m getting some errors. Basically, I have this code:
sql = ~SQL"""
select distinct on (e.id) e.id as row_id
from "pacman.public".entities as e
join "pacman.public".records as r on r.grantee_entity_id = e.id or r.grantor_entity_id = e.id
join "pacman.public".properties as p on p.rebuilt_id = r.rebuilt_id
where p.id > {{id}}
and e.status = 'done'
"""
I want to use it with Repo.query, so I convert it using to_sql:
{query, params} = to_sql(sql)
Repo.query(query, params)
The issue I’m seeing is that the query replaces the {{id}} with a ? instead of $1:
iex(pacman@node1.backend.core)24> to_sql(sql)
{"select distinct on (e.id) e.id as row_id from \"pacman.public\".entities as e join \"pacman.public\".records as r on r.grantee_entity_id = e.id or r.grantor_entity_id = e.id join \"pacman.public\".properties as p on p.rebuilt_id = r.rebuilt_id where p.id > ? and e.status = 'done'",
["019721cc-7271-7364-9bde-b8431d782359"]}
And, because of that, the query call fails:
iex(pacman@node1.backend.core)26> Core.Repo.query(query, params)
15:30:48.657 [debug] QUERY ERROR db=0.0ms
select distinct on (e.id) e.id as row_id from "pacman.public".entities as e join "pacman.public".records as r on r.grantee_entity_id = e.id or r.grantor_entity_id = e.id join "pacman.public".properties as p on p.rebuilt_id = r.rebuilt_id where p.id > ? and e.status = 'done'
{:error,
%Postgrex.Error{
message: nil,
postgres: %{
code: :syntax_error,
line: "1193",
message: "syntax error at or near \"and\"",
position: "254",
file: "scan.l",
unknown: "ERROR",
severity: "ERROR",
pg_code: "42601",
routine: "scanner_yyerror"
},
connection_id: 14304,
query: "select distinct on (e.id) e.id as row_id from \"pacman.public\".entities as e join \"pacman.public\".records as r on r.grantee_entity_id = e.id or r.grantor_entity_id = e.id join \"pacman.public\".properties as p on p.rebuilt_id = r.rebuilt_id where p.id > ? and e.status = 'done'"
}}
Why does SQL returns the query fields as ? instead of $1, $2, etc which is what ecto would expect?
