Hello, I am trying to do a postgrex query with the sql library where I get the first entry from a table sorted by last_visited and excluding all entries where url is in state.lock or host is in state.politeness.
{query, params} = ~SQL[from pages]
|> ~SQL[where url not in #{state.lock}]
|> ~SQL[where host not in #{state.politeness}]
|> ~SQL"select url"
|> to_sql()
{:ok, %Postgrex.Result{rows: [[response]]}} = Postgrex.query(:postgrex, query, params)
However, when I run this I get:
** (MatchError) no match of right hand side value: {:error, %Postgrex.Error{message: nil, postgres: %{code: :syntax_error, file: "scan.l", line: "1244", message: "syntax error at or near \"$0\"", pg_code: "42601", position: "40", routine: "scanner_yyerror", severity: "ERROR", unknown: "ERROR"}, connection_id: 7202, query: "select url from pages where url not in $0 where host not in $0"}}
{query, params} = ~SQL[from pages]
|> ~SQL[where not url = any?(#{state.lock}) and not host = any?(#{state.politeness})]
|> ~SQL"select url"
|> to_sql()
{:ok, %Postgrex.Result{rows: [[response]]}} = Postgrex.query(:postgrex, query, params)
so that I only have one where and also for your solution, and now I get:
** (MatchError) no match of right hand side value: {:error, %Postgrex.Error{message: nil, postgres: %{code: :undefined_parameter, file: "parse_param.c", line: "140", message: "there is no parameter $0", pg_code: "42P02", position: "44", routine: "variable_paramref_hook", severity: "ERROR", unknown: "ERROR"}, connection_id: 4546, query: "select url from pages where not url = any ($0) and not host = any ($0)"}}
** (MatchError) no match of right hand side value: {:error, %Postgrex.Error{message: nil, postgres: %{code: :syntax_error, file: "scan.l", line: "1244", message: "syntax error at or near \"$0\"", pg_code: "42601", position: "40", routine: "scanner_yyerror", severity: "ERROR", unknown: "ERROR"}, connection_id: 5621, query: "select url from pages where url not in $0 and host not in $1"}}
with
{query, params} = ~SQL[from pages]
|> ~SQL[where url not in #{state.lock} and host not in #{state.politeness}]
|> ~SQL"select url"
|> to_sql()
{:ok, %Postgrex.Result{rows: [[response]]}} = Postgrex.query(:postgrex, query, params)
You added a space that shouldn’t be there after any.
Taking a step back, are you sure this is the right library for you? The sql library seems aimed at people familiar with SQL and who prefer to use that syntax instead of the ecto query syntax. It doesn’t seem like this is syntax you’re super comfortable with though and the process of using ecto would be a lot simpler for what you’re doing.
Your problem is the SQL. Take the SQL you are writing and put it in Postgres, you will get an error. Make sure to use parameters and not just write out the IN manually.
You can’t use parameters with IN like that; this is a Postgres limitation not an Elixir one. This is why you should be using any and not in when using parameters.
Hey there, I’ve been hard at work on the next version. Do you mind trying out main with the new adapters:
use SQL, adapter: SQL.Adapters.Postgres
~SQ[....]
I’m aiming to get a release out next week after I have updated all the docs, but as we speak the library is conformant to SQL 2016 standard, with over 900 generated tests.
The version after this will focus on improving the API. So that we might be able to accumelate where clauses as you tried in your prior attempt.
