Binding with IN operator

[gavinbelson]

We are trying to use useBind() with a sequelize IN raw query going to MSSQL.

This works great if status has one element. However, when there are multiple elements in status:

status = ["Completed","In Progress"]
query.append(SQL WHERE status IN ( ${status} ))
statuses = await db.query(query.useBind(), {raw: true, nest: true});

The query doesn't return anything. The log from sequelize shows:

{"plain":false,"raw":true,"nest":true,"bind":[["Completed","In Progress"]],"type":"SELECT","level":"info","message":"Executing (default): SELECT * FROM some_table WHERE status IN ( @0 )"}

If I take out the .useBind() the query works as expected, uses replacements, and returns both statuses of "Completed" and "In Progress"

[felixfbecker]

I am not familiar with MSSQL, but usually you can’t use a single placeholder to replace all values passed to IN. You would need one placeholder per value, but you don’t know the number of values, so it’s not the same prepared query anymore for different values. In Postgres for example this doesn’t work with IN, but it does work with ANY, because ANY takes a single array value as operand that you can use a single placeholder for.

[felixfbecker]

It works without useBind() because then Sequeluze will just generate a different query. If you leave it in, Sequelize will pass the query with placeholders and values to the DB, and the DB won’t know what to do with a single placeholder in IN and an array of values.

[gavinbelson]

That makes sense, but a lot of the time, when you are doing bindings, it needs to be used with the IN operator.

So, right now there is no way of doing bindings with the IN operator right?

[felixfbecker]

No, it's impossible.

[DblK]

Maybe you can use appendIn See #141