How Can I Make Dynamic Query In Sequelize with nodeJs

Question

I want Query Function That I can pass in FindAll Query To Get Daynamic data

Example: If I want to get data like age>5 then direct I can get using query paramas

This is just example

At the end I want query funcatuion that can genrate dynamic query on query params

I have made this funcation for daynamic sort, and paggination

const usersqquery = (q) => {
  const limit = q?.limit * 1 || 200;
  const page = q?.page * 1 || 1;
  const skip = (page - 1) * limit;
  const sort = q?.sort || "createdAt";
  const sortBy = q?.sortBy || "DESC";

if (q?.limit) {
    return { order: [[sort, sortBy]], limit, offset: skip };
  }
  return { order: [[sort, sortBy]] };
};

I want more acurate funcation for genrate dynamic query

Answer 1

i've been through the same problem but in my case with typeorm but i think my solution will works for you. what i did is implement simple parser that take stingifiedJsonObjectQuery which is JSON string that represent where from client and parse it's to typeorm object. that's snippet from my production code that does the jobs:
(and please note that this is not for copy paste it just to looks at and to learn from)

import { BadRequestException, Inject } from '@nestjs/common';
import { Between, Equal, FindOneOptions, ILike, In, IsNull, LessThan, MoreThan } from 'typeorm';
import { ILogger } from '../../core/logger/logger.interface';
import { LOGGER_PROVIDER_NAME } from '../../core/providers.constants';
import { IQueryParser, ITypeOrmQueryParser } from './queryParser.interface';

export enum FindOperators {
  NOT = '$not',
  ILIKE = '$ilike',
  IN = '$in',
  MORE_THATN = '$MoreThan',
  LESS_THAN = '$LessThan',
  EQUAL = '$Equal',
  ISNULL = '$ISNULL',
  ARE = '$are',
  CONTAINS = '$contains',
  BETWEEN = '$between',
  Assigned = '$Assigned',
}

export interface ICondition {
  operation?: FindOperators;
  value?: any;
}

function parseOperator(condition: ICondition) {
  if (typeof condition !== 'object') return condition;
  if (condition === null || condition === undefined || condition.value == null) return IsNull();
  if (!condition.operation) return condition.value;
  switch (condition.operation) {
    case FindOperators.EQUAL:
      return Equal(condition.value);
    case FindOperators.LESS_THAN:
      return LessThan(condition.value);
    case FindOperators.MORE_THATN:
      return MoreThan(condition.value);
    case FindOperators.IN:
      return In(condition.value);
    case FindOperators.ILIKE:
      condition.value = condition.value.replace('%20', ' ');
      return ILike(condition.value);
    case FindOperators.ARE:
      return {
        id: In(condition.value),
      };
    case FindOperators.CONTAINS:
      condition.value = condition.value.replace('%20', ' ');
      return ILike('%' + condition.value + '%');
    case FindOperators.BETWEEN:
      return Between(new Date(condition.value[0]), new Date(condition.value[1]));
    default:
      throw new BadRequestException(`Operator ${condition.operation} not found`);
  }
}

function parseAndOptions(where) {
  for (const [key, value] of Object.entries(where)) {
    if (typeof value === 'object' && value !== null) {
      if (value['operation']) {
        where[key] = parseOperator(value);
        continue;
      }
      where[key] = parseAndOptions(value);
    }
  }
  return where;
}

function parseOrOptions(where) {
  if (Array.isArray(where)) {
    for (let i = 0; i < where.length; i++) where[i] = parseOrOptions(where[i]);
    return where;
  }

  return parseAndOptions(where);
}

function parseTypeOrmOptions(options: FindOneOptions) {
  if (options.where) options.where = parseOrOptions(options.where);
  return options;
}

export class TypeOrmQueryParser implements ITypeOrmQueryParser {
  constructor(@Inject(LOGGER_PROVIDER_NAME) private _logger: ILogger) {}

  parseQuery(str: any) {
    const result: any = str;
    try {
      result.parsedOptions = JSON.parse({ ...result }.options);
      result.options = parseTypeOrmOptions(JSON.parse(JSON.stringify(result)).parsedOptions);
    } catch (err) {
      this._logger.error('[TYPEORM_PARSER]', 'ERROR WHILE PARSING ' + err);
      result.options = {
        where: {},
      };
    }

    result.skip = parseInt(result.skip);
    if (!result.skip) {
      result.skip = 0;
    }
    result.limit = parseInt(result.limit);
    if (!result.limit) {
      result.limit = 100;
    }

    return result;
  }
}

export class SqlQueryParser implements IQueryParser {
  parseQuery(str: any) {
    const result: any = str;
    result.skip = parseInt(result.skip);
    if (!result.skip) {
      result.skip = 0;
    }
    result.limit = parseInt(result.limit);
    if (!result.limit) {
      result.limit = 100;
    }
    if (result.options) {
      try {
        result.options = JSON.parse(result.options);
        result.options = parseTypeOrmOptions(result.options);
      } catch (err) {
        result.options = {};
      }
    }

    return result;
  }
}