namedPlaceholders with IN clause

[steven10172]

It would be nice to have namedPlaceholders convert arrays of data into an IN clause.

I would like the following to be possible:

var parms = {
   keys: [1,2,3,4,5,6,7]
};

db.pool.execute('SELECT id FROM table WHERE id IN (:keys)', parms, function(res, err) {
});

It should execute to the following:

SELECT id FROM table WHERE id IN (?,?,?,?,?,?,?)

[ETPac]

Is there a way to query an in clause? When I try to run something like the above statement it only runs the first key in the group. I think there is a bug that only uses the first item in the array even if its a string

[sidorares]

There is currently 'arrayToList' in SqlString, but it seems that it does not work for example like mysql.query("select * from mysql.user where Host in ?", [['localhost', '127.0.0.1']], ... ), I'll look into code/documentation more

@steven10172 - I'd like to keep named placeholders semantics close to unnamed, so Ideally we need to make unnamed ? to work with array parameters

[sidorares]

@steven10172 for binary protocol ( prepared statements ) one placeholder always represent one prepared statement parameter.

// text protocol, works without helper:
db.query("select * from mysql.user where Host in (:hosts)", { hosts: ['localhost', '127.0.0.1'] }, ...);

// binary protocol: hosts is one single parameter, serialised as `['localhost', '127.0.0.1'].toString()` 
db.execute("select * from mysql.user where Host in (:hosts)", { hosts: ['localhost', '127.0.0.1'] }, ...);
// result is execute "select * from mysql.user where Host in (?)" with "localhost,127.0.0.1" as parameter
// probably not what you expect

I'd probably suggest db.query() for examples like this. Alternatively, you can build query dynamically:

var hosts = ['localhost', '127.0.0.1'];
db.execute("select * from mysql.user where Host in (" + hosts.map( () =>'?' )+ ")", hosts, cb);

[Dadibom]

Anything new regarding this issue?