Skip to content
Advertisement

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

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?

Advertisement

Answer

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.

2 People found this is helpful
Advertisement