Querying by list of IDs in Ecto / Postgres

Questions / Help
,
1

Not sure why this doesn’t work, can’t figure out a way to interpolate a list of IDs into this simple query:

Ecto.Adapters.SQL.query!(
  MyApp.Repo, "SELECT * FROM users WHERE id IN ($1)", [id_list]
)

Have tried id_list = [1, 2], and just about every other combination, but I can’t seem to get Ecto to interpret it into $1 properly

2

Is there a reason you’re not using the query builder Ecto.Query.from/2? E.g.

from(u in UserSchema, where: u.id in ^id_list) |> Repo.all()

Alternatively you could try the query "SELECT * FROM users WHERE id = ANY($1)".

1 Like
3

I’m using Repo.all from u in User, where: u.id in ^id_list, but was trying to also see if we could interpolate the list into
"SELECT * FROM users WHERE id IN ($1)", [id_list], it seems like such a basic operation that we can’t write as raw SQL using Ecto/Postgres

4

I believe it’s due to PostgreSQL expecting a single argument in that position and Ecto/Postgrex sends a list (because you’re doing it manually, it doesn’t know what the DB wants). It would probably work if you sent a comma-separated string list (Enum.join(id_list, ",")) or used that = ANY($1) instead.

5

Haven’t tried ANY, but tried Enum.join earlier -> doesn’t work as Postgrex expects an integer for ($1)

6

Maybe it would work without the parentheses around $1 but I did not test. That makes sense though, PostgreSQL is reading it like you want to query id in (15), i.e. single value. If you wanted multiple, you’d have to use id in ($1, $2, $3).

1 Like
7

Ahh true, that is probably why it didn’t work… looking at an example query makes it seem obvious now SELECT * FROM users WHERE id IN (1, 2, 3)

8

Went back and tested just for the record, removing parens around $1 throws a syntax error, but "SELECT * FROM users WHERE id = ANY($1)" works

Going with the Ecto query anyway, that is much nicer to read :stuck_out_tongue: