When Recieving Key-Value pair, Where the Value is Array. Use IN Instead of =

[DanielBailey-web]

Feature Request: Enhanced object value substitution for WHERE clauses
It would be nice if this same query could be mapped to either = or IN depending on the type of input (array vs string/number)

SELECT * FROM users WHERE ?;

const userIds = [1, 2, 3, 4]; // or const userIds = 1; 
const users = (await con.query(sql, [{userId: userIds}]))[0]

This currently maps to the following string

SELECT * FROM users WHERE `userId` = 1, 2, 3, 4;

Which works fine for the single value example. But it would be nice if the value was an array it could map to:

 SELECT * FROM users WHERE `userId` in (1, 2, 3, 4);

This seems like it wouldn't be too hard, but I am unaware of any security vulnerabilities such a change could make.

I just want to cut down on the amount of functions I need to have to call specific tables.

For the first query I could obviously call it with a different key and it would still work such as

const emails = "[email protected]";
const users = (await con.query(sql, [{email: emails}]))[0]

This would prevent the need to have a file for emails IN (?) and userIds IN (?) etc.
But to my knowledge you cannot currently dynamically change the search param when using IN.

[dougwilson]

The object form for ? Is only designed for SET, which does not support IN. It also joins multiple kvps with a comma, not AND or OR or similar. There does not seems to be a feisable way for it to support both SET and WHERE syntax at the same time.

I would suggest using the toSqlString functionality to create more rich objects to control the specific seralization or a library thay can declare all the richness needed to dynamically build out a WHERE query.

[dougwilson]

If you just want to only provide a single key for the object format to form a WHERE query, you can easily have a helper that constructs this exact query, as it is very simple (just a single condition in the WHERE):

function SelectQuery(table, column, value) {
  return SqlString.format('SELECT * FROM ?? WHERE ?? IN (?)', [table, column, value])
}