Typeorm dynamic query builder from structured object

Based on Ben's answer, I tweaked a little the functions to allow a more versatile "filter" object:

// enum
export enum Operator {
  AND = 'AND',
  OR = 'OR',
}

// interfaces
interface FieldOptions {
  is?: string;
  not?: string;
  in?: string;
  not_in?: string;
  lt?: string;
  lte?: string;
  gt?: string;
  gte?: string;
  contains?: string;
  not_contains?: string;
  starts_with?: string;
  not_starts_with?: string;
  ends_with?: string;
  not_ends_with?: string;
}

export interface Field {
  [key: string]: FieldOptions;
}

export type Where = {
  [K in Operator]?: (Where | Field)[];
};

// functions
export const filterQuery = <T>(query: SelectQueryBuilder<T>, where: Where) => {
  if (!where) {
    return query;
  } else {
    return traverseTree(query, where) as SelectQueryBuilder<T>;
  }
};

const traverseTree = (query: WhereExpression, where: Where, upperOperator = Operator.AND) => {
  Object.keys(where).forEach((key) => {
    if (key === Operator.OR) {
      query = query.orWhere(buildNewBrackets(where, Operator.OR));
    } else if (key === Operator.AND) {
      query = query.andWhere(buildNewBrackets(where, Operator.AND));
    } else {
      // Field
      query = handleArgs(query, where as Field, upperOperator === Operator.AND ? 'andWhere' : 'orWhere');
    }
  });

  return query;
};

const buildNewBrackets = (where: Where, operator: Operator) => {
  return new Brackets((qb) =>
    where[operator].map((queryArray) => {
      traverseTree(qb, queryArray, operator);
    }),
  );
};

const handleArgs = (query: WhereExpression, field: Field, andOr: 'andWhere' | 'orWhere') => {
  ...
};

This way we now can have this kind of object as a query parameter:

{
  AND: [
    {
      OR: [
        {
          name: {
            is: 'John'
          },
        },
        {
          surname: {
            is: 'Doe'
          },
        }
      ]
    },
    {
      AND: [
        {
          age: {
            gt: 30
          },
        },
        {
          type: {
            not: 'Employee'
          }
        }
      ]
    },
    {
      registered_date: {
        gte: '2000-01-01'
      }
    }
  ]
}

The resulting query would be:

SELECT *
FROM users U 
WHERE (U.name = 'John' OR U.surname = 'Doe') AND (U.age > 30 AND U.type != 'Employee') AND U.registered_date >= '2000-01-01';

1. Split it up into 2 functions to make adding the types easier
2. In your case statements you need to do orWhere or andWhere
3. Instead of mapping over the brackets, lift it up one level

import { Brackets, WhereExpression, SelectQueryBuilder } from "typeorm";

interface FieldOptions {
  starts_with?: string;
  ends_with?: string;
  contains?: string;
}

interface Fields {
  email?: FieldOptions;
}

interface Where extends Fields {
  OR?: Fields[];
  AND?: Fields[];
}

const handleArgs = (
  query: WhereExpression,
  where: Where,
  andOr: "andWhere" | "orWhere"
) => {
  const whereArgs = Object.entries(where);

  whereArgs.map(whereArg => {
    const [fieldName, filters] = whereArg;
    const ops = Object.entries(filters);

    ops.map(parameters => {
      const [operation, value] = parameters;

      switch (operation) {
        case "is": {
          query[andOr](`${fieldName} = :isvalue`, { isvalue: value });
          break;
        }
        case "not": {
          query[andOr](`${fieldName} != :notvalue`, { notvalue: value });
          break;
        }
        case "in": {
          query[andOr](`${fieldName} IN :invalue`, { invalue: value });
          break;
        }
        case "not_in": {
          query[andOr](`${fieldName} NOT IN :notinvalue`, {
            notinvalue: value
          });
          break;
        }
        case "lt": {
          query[andOr](`${fieldName} < :ltvalue`, { ltvalue: value });
          break;
        }
        case "lte": {
          query[andOr](`${fieldName} <= :ltevalue`, { ltevalue: value });
          break;
        }
        case "gt": {
          query[andOr](`${fieldName} > :gtvalue`, { gtvalue: value });
          break;
        }
        case "gte": {
          query[andOr](`${fieldName} >= :gtevalue`, { gtevalue: value });
          break;
        }
        case "contains": {
          query[andOr](`${fieldName} ILIKE :convalue`, {
            convalue: `%${value}%`
          });
          break;
        }
        case "not_contains": {
          query[andOr](`${fieldName} NOT ILIKE :notconvalue`, {
            notconvalue: `%${value}%`
          });
          break;
        }
        case "starts_with": {
          query[andOr](`${fieldName} ILIKE :swvalue`, {
            swvalue: `${value}%`
          });
          break;
        }
        case "not_starts_with": {
          query[andOr](`${fieldName} NOT ILIKE :nswvalue`, {
            nswvalue: `${value}%`
          });
          break;
        }
        case "ends_with": {
          query[andOr](`${fieldName} ILIKE :ewvalue`, {
            ewvalue: `%${value}`
          });
          break;
        }
        case "not_ends_with": {
          query[andOr](`${fieldName} ILIKE :newvalue`, {
            newvalue: `%${value}`
          });
          break;
        }
        default: {
          break;
        }
      }
    });
  });

  return query;
};

export const filterQuery = <T>(query: SelectQueryBuilder<T>, where: Where) => {
  if (!where) {
    return query;
  }

  Object.keys(where).forEach(key => {
    if (key === "OR") {
      query.andWhere(
        new Brackets(qb =>
          where[key]!.map(queryArray => {
            handleArgs(qb, queryArray, "orWhere");
          })
        )
      );
    } else if (key === "AND") {
      query.andWhere(
        new Brackets(qb =>
          where[key]!.map(queryArray => {
            handleArgs(qb, queryArray, "andWhere");
          })
        )
      );
    }
  });

  return query;
};