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

Question

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

score 154 · Answer 1 · edited Jul 06 '17 at 02:19

154

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

edited Jul 06 '17 at 02:19

Alison R.

4,204
28
33

answered Mar 17 '16 at 14:59

Itai Noam

3,815
3
18
14

1

Thanks! I think it should be: if (req.query.param) { queryBuilder.where('somecolumn', req.query.param); } – alexP_Keaton Dec 09 '16 at 02:44
10

This is the perfect way of doing it. Absolutely should be the accepted answer. – th3n3wguy Dec 02 '18 at 22:58
2

Much better way than accepted answer! KNEX documentation should add this way of using .modify - to support conditionally applying query features – deksden May 19 '19 at 18:25
1

How does this work if you want to chain multiple potentials WHERE's on? Say I have 6 params that each could trigger an AND potentially. Or potentially a single WHERE, with any combination – Matt Weber Oct 21 '21 at 17:33
@eponymous23 please accept this as answer – Tim Bogdanov Jan 26 '22 at 17:13

score 53 · Accepted Answer · answered Dec 10 '14 at 13:09

53

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

answered Dec 10 '14 at 13:09

Simon Briche

1,304
11
6

3

Please also check the other anwer! – Botond Balázs Apr 02 '18 at 07:53
1

Can I ask why this is less upvoted? Is there a specific downside to this method? Because it seems much cleaner and allows for use of `await`. – bzupnick Apr 25 '19 at 19:02
9

@bzupnick the `.modify()` construction keeps you entirely within knex's provided Query Builder API, which seems cleaner than going "off road" with a JavaScript workaround. The knex docs state that `modify()` "Allows encapsulating and re-using query snippets and common behaviors as functions." Since you mention `await` as a desirable feature: The knex query with a `modify` clause is also `await`-able. – Matthew Marichiba May 15 '19 at 06:48
FYI for those using it, this method prevents `knex-paginate` from returning the `pagination` info in the response. If you're using `knex-paginate`, go with the `modify()` method instead. – djflorio Sep 21 '22 at 12:56

score 2 · Answer 3 · edited Feb 17 '23 at 20:41

2

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

edited Feb 17 '23 at 20:41

icc97

11,395
8
76
90

answered Nov 04 '19 at 07:28

Merey Nurlan

295
5
17

score -1 · Answer 4 · answered Jul 07 '21 at 07:04

-1

Most simplest solution is skipUndefined

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

answered Jul 07 '21 at 07:04

Jaspinder Singh

24
1

skipUndefined seems not to be defined in the current docs. I don't know if it's from an older version or if it's an undocumented feature. If it's the latter could you mention that in your answer? – Raif Dec 28 '21 at 15:24

score -11 · Answer 5 · answered Dec 04 '14 at 02:24

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

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

5 Answers5

Linked