Can I conditionally add a where() clause to my knex query?
Javascriptnode.jsknex.jsJavascript Problem Overview
I want to add a where()
clause in my query, but conditionally. Specifically, I want it added only if a sepecific querystring parameter is passed in the URL. Is this possible, and if so, how would I go about doing it?
router.get('/questions', function (req, res) {
knex('questions')
.select('question', 'correct', 'incorrect')
.limit(50)
.where('somecolumn', req.query.param) // <-- only if param exists
.then(function (results) {
res.send(results);
});
});
Javascript Solutions
Solution 1 - Javascript
Yes. Use modify.
As applied to your example:
router.get('/questions', function (req, res) {
knex('questions')
.select('question', 'correct', 'incorrect')
.limit(50)
.modify(function(queryBuilder) {
if (req.query.param) {
queryBuilder.where('somecolumn', req.query.param);
}
})
.then(function (results) {
res.send(results);
});
});
Solution 2 - Javascript
You can store your query in a variable, apply your conditional where clause and then execute it, like this :
router.get('/questions', function(req, res) {
var query = knex('questions')
.select('question', 'correct', 'incorrect')
.limit(50);
if(req.query.param == some_condition)
query.where('somecolumn', req.query.param) // <-- only if param exists
else
query.where('somecolumn', req.query.param2) // <-- for instance
query.then(function(results) {
//query success
res.send(results);
})
.then(null, function(err) {
//query fail
res.status(500).send(err);
});
});
Solution 3 - Javascript
You can actually use query builder inside .where() like so:
.where((qb) => {condition == true ? do something if true : do something if false })
IMO @ItaiNoam answer should be correct with .modify()
Solution 4 - Javascript
Most simplest solution is skipUndefined
Person.query()
.skipUndefined()
.where('firstName', req.query.firstName);
Solution 5 - Javascript
You can do it by checking if your query string is present and running a different query.
router.get('/questions', function(req, res) {
if (req.query.yourQueryString) {
// Run your more specific select
} else {
knex('questions').select('question', 'correct', 'incorrect').limit(50).where(
'somecolumn', req.query.param) // <-- only if param exists
.then(function(results) {
res.send(results);
});
}
}
});