1

I'm trying to use Ecto.Adapters.SQL.query, it works fine, but not for arrays. For example this statement fails:

Ecto.Adapters.SQL.query Repo, "SELECT p.* FROM posts p WHERE p.title in ($1)", 
  [["title1", "title2"]]

The error:

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

UPDATE

There's no easy way to do it, but it is not a limitation of Ecto, it is a limitation of SQL databases / PostgreSQL, more details and workaround.

It's hard to believe that in 2016 SQL databases still laking such a basic feature...

Community
  • 1
  • 1
Alex Craft
  • 13,598
  • 11
  • 69
  • 133

2 Answers2

5

If you're using Postgres you can also use ANY

Ecto.Adapters.SQL.query(
  Repo,
  "SELECT p.* FROM posts p WHERE p.title = ANY($1)", 
  [["title1", "title2"]]
)

And Postgres produces the same query plan for In vs Any

Community
  • 1
  • 1
Arrel
  • 13,558
  • 7
  • 26
  • 24
2

I think the answer to this question is pretty much the same from your previous question. Just use the in syntax from here.

Update

To run a raw sql query for your example, you can use the following:

Ecto.Adapters.SQL.query(MyApp.Repo, "SELECT p.* FROM POSTS p WHERE p.TITLE IN ($1, $2)", ["title1", "title2"])
NoDisplayName
  • 15,246
  • 12
  • 62
  • 98
  • Thanks, yes it's possible to do with Query DSL, but still - the ability to execute raw SQL seems to be quite fundamental to the SQL library. I'd like to see the answer - how to do it. – Alex Craft Mar 21 '16 at 07:44
  • But how to render the result into the view, i.e. how to make use of `Ecto.Adapters.SQL.query(MyApp.Repo,..`? How to pass the result to teh view? Thanks. – W.M. Jul 18 '16 at 21:55