Filtering Query Builder for Sequelize in Express

filter-query-builder.js

import { Op } from "sequelize";

/*
  SEQUELIZE FILTERING QUERY BUILDER

  SAMPLE URI
  /api/v1/ubello-products?page=1&n_product=20&PRICE_RETAIL[lte]=90000&PRICE_RETAIL[gte]=10&PRICE_SUPPLY[gte]=20000&PRICE_SUPPLY[lte]=50000&sort=PRICE_RETAIL&PRODUCT_NAME=%GIFT%
  
  SAMPLE req.query from express
  {
    page: '1',
    n_product: '20',
    PRICE_RETAIL: { lte: '90000', gte: '10' },
    PRICE_SUPPLY: { gte: '20000', lte: '50000' },
    sort: 'PRICE_RETAIL',
    PRODUCT_NAME: '%GIFT%'
  }

  SAMPLE BUILT QUERY
  {
    PRICE_RETAIL: { [Symbol(lte)]: '90000', [Symbol(gte)]: '10' },
    PRICE_SUPPLY: { [Symbol(lte)]: '50000', [Symbol(gte)]: '20000' },
    PRODUCT_NAME: { [Symbol(like)]: '%GIFT%' }
  }

  SAMPLE BUILT SORT QUERY
  [ [ 'PRICE_RETAIL', 'ASC' ] ]
*/

class FilterQueryBuilder {
  buildFilterQuery(valid_keyword, query) {
    const result = {};

    for (const key in query) {
      const value = query[key];

      // Remove not supported key
      if (!valid_keyword.includes(key)) {
        continue;
      }

      // Build query for the multiple operation for one key
      if (typeof value === "object") {
        result[key] = {};

        if (!!value.lte) {
          result[key][Op.lte] = value.lte;
        }
        if (!!value.gte) {
          result[key][Op.gte] = value.gte;
        }
        continue;
      }

      // Like Operation Query ex) %Cake%
      if (value.charAt(0) == "%" && value.charAt(value.length - 1) == "%") {
        result[key] = { [Op.like]: value };
        continue;
      }

      // simple search
      result[key] = value;
    }

    return result;
  }

  buildSortQuery(query) {
    if (!query) {
      return null;
    }

    if (query.charAt(0) == "-") {
      return [[query.split("-")[1], "DESC"]];
    } else {
      return [[query, "ASC"]];
    }
  }
}

export default new FilterQueryBuilder();