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);
});
});
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);
});
});
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);
});
});
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()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With