CODEWITHSUNDEEP
javascriptnode.jsknex.js
eponymous23
eponymous23

Reputation: 1138

Can I conditionally add a where() clause to my knex query?

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

Upvotes: 64

Views: 43765

Answers (5)

Merey Nurlan
Merey Nurlan

Reputation: 305

You can actually use queryBuilder inside .where() like so:

.where((queryBuilder) => {condition === true ? do something if true : do something if false })

IMO @ItaiNoam's answer should be the correct one with .modify()

Upvotes: 2

Jaspinder Singh
Jaspinder Singh

Reputation: 22

Most simplest solution is skipUndefined

Person.query()
  .skipUndefined()
  .where('firstName', req.query.firstName);

Upvotes: -2

Itai Noam
Itai Noam

Reputation: 4155

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

Upvotes: 166

Simon Briche
Simon Briche

Reputation: 1322

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

Upvotes: 55

Josh Lankford
Josh Lankford

Reputation: 468

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

Upvotes: -11

Related Questions