Conditional filter using Knex.js and SQL with multiple search criteria

Question

I have a database of items that my user needs to be able to search. They can apply different filters such as category, searchTerm, itemType.

I know how to apply each of these filters in a knex query, but am not sure how to combine them conditionally. E.g. in one case, the user inputs only searchTerm. So I do not want to filter by category or item type, but only searchTerm. However, I want to have only one knex function which conditionally adds extra clauses to the query.

Something like:

const getFilteredItems = (searchCriteria) => knex('items')
   // IF (searchCriteria.searchTerm)
  .where('items.itemName', 'like', `%${searchCriteria.searchTerm}%`)
  // IF (searchCriteria.itemType)
  .where('items.itemType', '=', searchCriteria.itemType)
  // IF (searchCriteria.category)
  .where('items.category', '=', searchCriteria.category)

But I am not sure how to conditionally append the extra where clauses to the knex query. Is there an elegant way to do this without writing raw SQL?

Answer 1

You can conditionally build your query with the knex query builder:

const getFilteredItems = (searchCriteria) => knex('items')
  .where((qb) => {
    if (searchCriteria.searchTerm) {
      qb.where('items.itemName', 'like', `%${searchCriteria.searchTerm}%`);
    }

    if (searchCriteria.itemType) {
      qb.orWhere('items.itemType', '=', searchCriteria.itemType);
    }

    if (searchCriteria.category) {
      qb.orWhere('items.category', '=', searchCriteria.category);
    }
  });

You can also use this instead of accepting a querybuilder argument as in the example @abdulbarik gave, but I think it is less explicit.

Answer 2

You can try the following :

const getFilteredItems = (searchCriteria) => knex('items')
  .where('items.itemName', 'like', `%${searchCriteria.searchTerm || ''}%`)
  .where('items.itemType', 'like', `%${searchCriteria.itemType || ''}%`)
  .where('items.category', 'like', `%${searchCriteria.category || ''}%`)

If any parameter is missing or undefined, it will match the field to be like '%%' which effectively means, don't care about this parameter.

Answer 3

Try orWhere and update your query on this basis

const getFilteredItems = (searchCriteria) => knex('items')
   // IF (searchCriteria.searchTerm)
  .where('items.itemName', 'like', `%${searchCriteria.searchTerm}%`)
  // IF (searchCriteria.itemType)
  .orWhere('items.itemType', '=', searchCriteria.itemType)
  // IF (searchCriteria.category)
  .orWhere('items.category', '=', searchCriteria.category)

Example

knex('users').where(function() {
  this.where('id', 1).orWhere('id', '>', 10)
}).orWhere({name: 'Tester'})