CODEWITHSUNDEEP
sqlelixirecto
Alex Craft
Alex Craft

Reputation: 15366

How to use Ecto.Adapters.SQL.query with Arrays?

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...

Upvotes: 1

Views: 1744

Answers (2)

Arrel
Arrel

Reputation: 13658

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

Upvotes: 5

NoDisplayName
NoDisplayName

Reputation: 15746

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"])

Upvotes: 2

Related Questions