CODEWITHSUNDEEP
javascriptmysqlnode.jsnode-mysql
David Jones
David Jones

Reputation: 10229

MySQL query via Node.js with an unknown number of null parameters

I'm 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 of the query parameters are defined, but not all of them? This is a very common use case, so I'm guessing there is an elegant way to account for this, but this is all I can seem to come up with:

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 every possible combination is obviously a crude way to do it, and really isn't very practical if there are more than just a couple query parameters. Is there a more elegant (i.e. concise) way to account for an unknown number of null parameters?

UPDATE

This is what ended up working 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);
});

Upvotes: 3

Views: 948

Answers (2)

jcaron
jcaron

Reputation: 17710

Build the 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'll need to handle the case where there are no conditions (you'll have to pick whether all or none match).

Upvotes: 0

Eran Globen
Eran Globen

Reputation: 461

This may be a bit cumbersome but you can use this query:

query = "SELECT * FROM user WHERE (? IS NULL || first = ?) AND (? IS NULL || last = ?)";

Just be sure at least one parameter is not null (either in the query or in js).

ETA: If you use the named parameter format (:param instead of ?) this will save you some repetition as well.

Upvotes: 4

Related Questions