CODEWITHSUNDEEP
sqlelixirecto
Alex Craft
Alex Craft

Reputation: 15356

Ecto SQL Fragment fails, why?

Trying to execute SQL fragment:

Repo.all from p in Posts, where: fragment("lower(?) in ?", p.title, ^["some-title"])

But it fails, it generates following SQL and the error:

SELECT p0."title" FROM "posts" AS p0 WHERE (lower(p0."title") in $1) [["some-title"]]

** (Postgrex.Error) ERROR (syntax_error): syntax error at or near "$1"

UPDATE: SOLUTION

So, after lots of trials I figured out how to use it:

Repo.all from p in Posts, where: fragment("lower(?)", p.title) in ^["some-title"])

But still - why the original expression didn't worked? It seems like it's completely valid too.

UPDATE

There should be parentheses after in

I tried it, didn't works either:

Repo.all from p in Posts, where: fragment("lower(?) in (?)", p.title, ^["some-title"])

** (ArgumentError) Postgrex expected a binary that can be encoded/cast to type "text", got ["some-title"]. Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.

Upvotes: 1

Views: 745

Answers (1)

NoDisplayName
NoDisplayName

Reputation: 15746

1) Yes, the right way to go is:

Repo.all from p in Posts, where: fragment("lower(?)", p.title) in ["some-title"])

2) The original query does not work because it generates wrong SQL syntax. It generates something like:

... where lower(p.title) in ["some-title"] ...

where the right syntax is:

... where lower(p.title) in ('some-title') ...

Upvotes: 2

Related Questions