Search code examples
sqlelixirecto

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

Solution

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