MySQL query via Node.js with unknown number of null parameters
I am using this MySQL plugin for Node.js. Here's a very simple query:
db.query("SELECT * FROM user WHERE first = ? AND last = ?", [req.query.first, req.query.last], function(error, results) {
console.log(results);
});
Now here's the problem: what if only some query parameters are defined , but not all of them ? This is a very common use case, so I guess there is an elegant way to account for this, but this is all I can think of:
var query;
if (first && !last) {
query = "SELECT * FROM user WHERE first = ?";
} else if (!first && last) {
query = "SELECT * FROM user WHERE last = ?";
} else if (first && last) {
query = "SELECT * FROM user WHERE first = ? AND last = ?";
}
db.query(query, [req.query.first, req.query.last], function(error, results) {
console.log(results);
});
Writing a separate query for each possible combination is obviously a crude way to do this, and in fact it is not very practical if there are multiple pair of query parameters. Is there a more elegant (i.e. concise) way to account for an unknown number of null parameters?
UPDATE
Here's what ended up for me:
db.query("SELECT * FROM user WHERE (:first IS NULL || first = :first) AND (:last IS NULL || last = :last)", {first: req.query.first, last: req.query.last}, function(error, results) {
console.log(error);
});
source to share
It might be a little cumbersome, but you can use this query:
query = "SELECT * FROM user WHERE (? IS NULL || first = ?) AND (? IS NULL || last = ?)";
Just make sure at least one parameter is not null (either in the request or in js).
ETA: If you use the named parameter format (: param instead of?) It will also save you some repetition.
source to share
Create your query dynamically. Something like:
var params = ["first", "last"];
var conds = [];
var args = [];
var i;
for (i = 0 ; i < params.length ; i++)
{
var p = params[i];
if (req.query[p] !== undefined)
{
conds.push(p+'=?');
args.push(req.query[p]);
}
}
var query = 'SELECT * FROM user WHERE '+conds.join(' AND ');
db.query(query,args, etc.);
You will need to handle the case where there are no conditions (you will need to choose if all are the same or not).
source to share