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

Question

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);
});

Eran Globen · Accepted Answer · 2014-09-06 23:31:48Z

4

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.

answered Sep 6, 2014 at 23:31

Eran Globen

4612 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jcaron · Accepted Answer · 2014-09-06 23:32:16Z

0

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).

answered Sep 6, 2014 at 23:32

jcaron

17.8k6 gold badges36 silver badges47 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related