How we write our query filter engine on our REST API (part 3)

In the previous blog post (part 2), I covered the query language and how to build a middleware, condition parser, and field parsers.

In this final post, I am going to cover the two last important layers–filter objects and the generic db query function. These last two layers are the final pieces of the puzzle.

Normalize the data to filter object

Now, we have our matrix and our entities parser so we just need to build our object filter that will be stored in our req (our any global variable) and will be used to create the db query.

To do that, we just need to run over each row of the matrix (separate with “or operator”) and each column (separate with “and operator”). Let’s look at the following function that creates the filter object:

//this is a type that define the avilable DB model that can be filter
//Employee is the model that we define in the top of this article
type ModelNameToDBModel = {
  'Employee': Employee; 
};

//extract all keys and add suffix to them we need suffix because there might be cases that there are same filed over couple of entities and we need to know which enntity it's (because we use it in a generic funtion)
export type FilterByModel<T extends Model, Suffix extends string> = {
  [key in keyof T as T[key] extends string | number | null | boolean | Date
    ? key extends string
      ? `${key}${Suffix}`
      : key
    : never]?: SearchFilter<T[key]>;
};

export type AndConditionFilters = {
  [key in keyof ModelNameToDBModel]?: FilterByModel<ModelNameToDBModel[key], key>;
};
//we sort all the conditions by conditionIndex ( condition index is hold the index of the condition in the search query)
  //we need the order of the condition for the matrix 
  const searchObjResult = conditionsSearchArray // in our case is just the result of employeeParser
    .sort((searchResult, nextSearchResult) => searchResult.index - nextSearchResult.index)
    .map(({ index, ...rest }) => rest);

//we run over each condition row
return conditionMatrix.map((conditionArray) => {
    const filter: AndConditionFilters = {};
  //we run over each each condition column  
    conditionArray.forEach((conditionIndex) => {
      const condition = searchObjResult[conditionIndex];
      if (condition) {
        //payment intent filter
//isConditionType is a type guard function that get the condition and suffix filed it's return true if it's the entity (in case we support couple of entities)
        if (isConditionType<'Employy'>(condition, PaymentIntentSearchWithSuffix)) {
          filter.employe = { ...filter.employe, ...condition }; //we combain all the filter for employee each row (if we had to support more entity it will becaome here
        }
      } else {
        const notSupportedCondition = ConditionIndexToConditionKey(queryString, conditionIndex);
        if (notSupportedCondition) {
          throw querySearchFieldNotSupported(notSupportedCondition);
        }
        throw querySearchFieldError;
      }
    });
    return filter;
  });

This function will return an array of object filters (each entity that can be filtered will be the key and the values will be the supported filter files). In our example, we only have employee api’s and the supported fields are: OnVacation, salary, numberOfDaysInOffice, firstName, lastName. With that object, we will create our db query.

Just to be clear, in case we have more entities that we support (and also nested entities), we would get them in the object filter so each entity would be key in the object (in case we got it in the search query, of course).

So the end result is an array of objects where each cell in the array is a filter object that combines our filter and is separated with “and operator” and each row separated with “or operator”.

[
 [1, 2, 4],
  [3, 4]
]
((isOnVacation:true&salary<1000)|numberOfDaysInOffice=10)&firstName:’Robert’

[
 employee: {
  isOnVacation:{
          action: Equal, //i wrote Equal but it will be the enum value
          value: true
         },

  salary: {
       action: LowerThen,
       value: 1000
      }
  firstName: {}//you can complete it by your own :)
 },
 employee: {
  numberOfDaysInOffice: {},
  firstName: {}
 }
]

We run this function in the middleware, depending on the api entity that we got from the req object (for reference, please check the middleware section).

We are almost done 🙂. We just need to create a generic function that gets the filter object array and returns a db query object (db where structure) and then we can use it in our services in a generic way.

Generic functions that get the filter object array and return a db query

I will keep this function simple, although it can get a little bit complex depending on how generic you want it to be.

We are going to get the array of object filters, then we will iterate the array and create a new Sequelize with array. After that, we will put in with “or operator| and then the query will be ready.

//simple function that get search action (define before) and return sequlize operation
const actionEnumToSequelizeAction = (action: SearchAction) => {
  switch (action) {
    case SearchAction.equal:
      return Sequelize.Op.eq;
    case SearchAction.greaterThan:
      return Sequelize.Op.gt;
  //i took care only for two action in the exmple but it's really easy to complate the rest
  }
};
export const getWhereCondition = <T extends | string | number | boolean>(
  dbKey: string,
  searchField?: SearchFilter<T>,
): Sequelize.WhereOptions | undefined => {
  if (!searchField) {
    return;
  }
  const where: Sequelize.WhereOptions = {};

//we are getting the sequelize action 
  const action = actionEnumToSequelizeAction(searchField.action);

  where[dbKey] = {
    [action]: searchField.value,
  };
  return where;
};

const modelNames = ['Employee'] //we will add the rest of our models here
const getFilterCondition = (
  filtersObject: FilterObject,
): Sequelize.WhereOptions => {
  let whereCondition: Sequelize.WhereOptions = {};
//this is a type that define the avilable DB model that can be filter
//for reference return to normalize the data to filter object section
  let modelKey: keyof ModelNameToDBModel;

//we are iterate on all the filter object  
  for (modelKey in filtersObject) {
    const filterObject = filtersObject[modelKey];
  //as you remember if we support more nasted entites, it's might be that two entities will have the same 
  //field so we add to all keys the model as suffix so we need to remove it to get the db field name
    const dbModalKeysRegex = new RegExp(`(.*)${modelNames.join('|')}`);

    Object.entries(filterObject!).forEach(([whereKeyWithSuffix, value]) => {
      const whereKey = whereKeyWithSuffix.replace(dbModalKeysRegex, '$1');

//we insert the new condition each time
      whereCondition = {
        ...whereCondition,
        ...getWhereCondition(whereKey, value),
      };
    });
  }
  return whereCondition;
};


export const getSequelizeWhereCondition = ({
  searchConditions,
}: {
  filtersObject?: FilterObject[];
  
}) => {
 //iterate over our filter objet array and for each filter object we are creating the where condition
  const whereSearchCondition = filtersObject
    ?.map((filterObject) => getFilterCondition(filterObject))
    .filter((condition) => !!condition);

//seperate the where array with Or,this is the way you do it in sequelize in case you use anouther Orm change it.
  const whereSearchOrCondition =
    whereSearchCondition && whereSearchCondition.length > 0 ? { [Sequelize.Op.or]: whereSearchCondition } : undefined;

  return whereSearchOrCondition
};

Now that we have all our necessary functions. we can start implementing the filter on our get request. The middleware will automatically add the filter object array to our req.search variable and then we can use getSequelizeWhereCondition to get the condition. The last thing is to use the db to get the data.

Any time we can easily support more fields and more entities (nested entities), we need to add it to the parsers and everything works automatically. We can also support more actions: start with, like, and more.

What is next:

• You can now build a parsing package for your clients, so that developers can use your query language. It’s really easy and it’s possible to create a package that will export a parser function that will do all the work with type safety. The function will get an array of conditions and will return a query search string that can be used as a filter query.
• The next thing that we want to do (it’s not mandatory) is to add support for more ways to use our filters. For example, support Sequelize (or mongo) DSL query filter. The advantage of using DSL is that you only need to export a type of your object structure and then the client will use JSON.stringify to send their object to the search query. Once you think of your DSL structure, the only thing you need to support is to parse it to our query language that we already implemented. Let’s look at an example: we want to filter all the users with first name “David”, whose salary is lower than 3k or who are on vacation.

{
  firstName: "David",
  $or: [{salary: {$lt: 3000}}, {isOnVacation: true}]
}

//the parser will return the query

const returnQeury = 'firstName:’David’&(salary<300|isOnVacation:true)'

In these three posts, we showed what the problem is that we are trying to solve and strategies to solve it. We also implemented the query language solution by deciding what our query language is, building layers that will handle our search query:

• Middleware — validate the query and call the factory depending on the entity.
• Factory function — call the relevant parser, creating the array filter object and storing it in global variable (the request in our case).
• Inside the entity api call we use the getSequelizeWhereCondition function to get the condition to use in our DB.

We also showed that once we implement it, it’s really easy to support more query languages such as Sequelize DSL or any other query language.

Visit our career website