Dynamic-Query V1.0.2

Description

This library provides a way to build mongo aggregation queries dynamically. It is based on a dictionary system that supports the builder engine to intercept the correct fields and operators in order to build query request. It also provide to aggregate other collection if necessary.

The goal of this package is to encapsulate query building and provide a simple way to develop a quick BE-Query system.

Known issues

• OR and AND operators are not differentiate by each other.
• OR operator as top level operator to multi field not supported.

Documentation

In order to properly use Dynamic-Query you need to write and register your own dictionary for the collections you are intented to query. The builder receives a standard request object and return the query based on values of the request.

Installation

npm i --save @alfercom/dynamic-query

Usage

Collection Example

@Entity()
export class MyEntity implements MyEntityInterface {
    @Field()
    field: string;
    @field()
    otherField: number;
}

Related Dictionary

const MY_ENTITY_DICTIONARY: Dictionary = [
  // STANDARD DICTIONARY ELEMENT
  {
    alias: "FIELD_ALIAS",
    field: "field",
    fieldType: "string",
    customOp: null,
    toExclude: false,
    dependOn: null,
  }

  // CUSTOM DICTIONARY ELEMENT
  {
    alias: "OTHER_FIELD_ALIAS",
    field: "otherField",
    fieldType: "number",
    customOp: {
      $lookup: {
        from: "otherCollection",
        localField: "otherField",
        foreignField: "_otherField",
        as: "otherCollection",
      },
      {
        $addFields: {
          customField: {
            $arrayElemAt: ["$otherCollection", 0],
          },
        },
      }
    },
    toExclude: false,
    dependOn: null,
  }

  // DICTIONARY ELEMENT THAT DEPENDS ON ANOTHER ELEMENT
  {
    alias: "CUSTOM_FIELD_ALIAS",
    field: "customField",
    fieldType: "string",
    customOp: null,
    toExclude: false,
    dependOn: "OTHER_FIELD_ALIAS",
  }

  // It's possible to exclude a dictionary element to prevent that it is used in the query if not explicity requested
  {
    alias: "SORT_ALIAS",
    field: "customField",
    fieldType: "number",
    customOp: { $sort: { customField: -1 } },
    toExclude: true,
    dependOn: null,
  }
]

Register your dictionary

There are two way to register you dictionary

1. Using the register() function to register single dictionary and subscribe it to dictionary map

import { register } from 'dynamic-query';

register(ENTITY_DICTIONARY);

2. Using the registerDictionaies() function implemented in the QueryBuilderFactory class

import { QueryBuilderFactory } from 'dynamic-query';

QueryBuilderFactory.registerDictionaries([MY_DICTIONARY, MY_OTHER_DICTIONARY]);

Query Builder Factory

This library provide a factory abstract class that can return the proper query builder based on entity you are intented to query.

//Get the query builder for MyEntity
const queryBuilder = QueryBuilderFactory.getBuilder(MyEntity);

• NOTE: If you don't pass any argument to getBuilder method or if the instance don't match a registered dictionary, it will return the default query builder proxy that provide a default implementation for the build method.
• NOTE (since 1.0.0): it's possible tell factory that have to return a proxy simply passign true as second argument.

//Get the query builder for MyEntity
const queryBuilderProxy = QueryBuilderFactory.getBuilder(MyEntity, true);

Query Builder

After invocating the factory you can use the builder to build the query .

function myAggregationFunction(request: DynamicQueryRequest): any {
  const queryBuilder = QueryBuilderFactory.getBuilder(MyEntity);
  const otherMatch = {
    otherField: {
      $in: ['value1', 'value2']
    }
  };
  const parseOtherMatch = false
  const query = queryBuilder.build(request, otherMatch, parseOtherMatch);
  /**
   * Remain function logic here...
   */
}

EXAMPLE

First step: Define Entities

@Entity('User')
export class User implements UserInferface {
  @Field()
  id: ObjectId;
  @Field()
  premiumAccount: boolean;
  @Field()
  name: string;
  @Field()
  email: string;
  @Field()
  active: boolean;
  @Field()
  createdAt: Date;
  @Field()
  updatedAt: Date;
}

@Entity('UserDetail')
export class UserDetail implements UserInterface {
  @Field()
  id: ObjectId;
  @Field()
  userId: ObjectId;
  @Field()
  age: number;
  @Field()
  birthDate: Date;
  @Field()
  address: {
    street: string;
    number: number;
    city: string;
    state: string;
    country: string;
  };
}

Second step: Define Dictionary

export const USER_DICTIONARY: Dictionary = [
  {
    alias: "USER_NAME",
    field: "name",
    fieldType: "string",
  },
  {
    alias: "USER_EMAIL",
    field: "email",
    fieldType: "string",
  }
  {
    alias: "IS_ACTIVE",
    field: "active",
    fieldType: "boolean",
    customOp: (value: "YES" | "NO") => {
      const valueToMatch = value === "YES" ? true : false;
      return { $match: { active: valueToMatch } };
    },
  },
  {
    alias: "CREATION_DATE",
    field: "createdAt",
    fieldType: "date",
  }
  {
    alias: "USER_DETAIL"
    field: "userDetail"
    fieldType: "object"
    customOp: {
      $lookup: {
        from: "UserDetail",
        localField: "id",
        foreignField: "userId",
        as: "userDetail",
      },
      {
        $addFields: {
          userDetail: {
            $arrayElemAt: ["$userDetail", 0],
          },
        },
      }
    },
  }
  {
    alias: "USER_AGE",
    field: "userDetail.age",
    fieldType: "date",
    dependOn: "USER_DETAIL",
  }
  {
    alias: "AGE_ASC",
    field: "userDetail.age",
    fieldType: "date",
    customOp: { $sort: { "userDetail.age": 1 } },
    toExclude: true,
  }
  {
    alias: "AGE_DESC",
    field: "userDetail.age",
    fieldType: "date",
    customOp: { $sort: { "userDetail.age": -1 } },
    toExclude: true,
  }
]

Third step: Register your dictionary

import { register } from 'dynamic-query';

register(USER_DICTIONARY);

Fourth step: Build your query

INPUT: request:

• The request object that contains the filters and the sort.

const request: DynamicQueryRequest = {
  filters: [
    {
      name: "NAME_FILTER",
      field: "USER_NAME",
      value: ["John", "Jane"],
      condition: "IN", // can also pass EQUAL
    }
    {
      name: "AGE_FILTER",
      field: "USER_AGE",
      value: ["13/07/1994"],
      condition: "LESS_OR_EQUAL"
    }
  ],
  offset: 0,
  limit: 10,
  order: "AGE_DESC"
};

Function Example:

• Get the builder from factory and call build method from dynamic-query-builder

export const findPremiumAccount = (request: DynamicQueryRequest): Recod<string, any>[] => {
  const otherMatch = { premiumAccount: true, active: true };
  const parseOtherMatch = false

  const queryBuilder = QueryBuilderFactory.getBuilder(User);

  queryBuilder.build(request, otherMatch, parseOtherMatch);

  const query = qUeryBuilder.getMainQuery();
  
  return query;
}

• otherMatch: The other match object that can be used to add additional match to the query.
• parseOtherMatch: In case you need to match value from a list, this param provides to parse your object to a mongo query object using $in operator, otherwise you can pass false to skip this step.

OUTPUT: query:

[
  {
    $match: {
      premiumAccount: true,
      name: {
          $in: ["John", "Jane"]
      }
    }
  }
  {
    $lookup: {
      from: "UserDetail",
      localField: "id",
      foreignField: "userId",
      as: "userDetail",
    },
    {
      $addFields: {
        userDetail: {
          $arrayElemAt: ["$userDetail", 0],
        },
      },
    }
  },
  {
    $match: {
      userDetail: {
        age: {
          $lte: new Date("1994-07-13T00:00:00.000Z")
        }
      }
    }
  },
  {
    $sort: {
    "userDetail.age": -1,
  },
  {
    $skip: 0,
  },
  {
    $limit: 10,
  }
]

TYPES

Default list Request

The standard request used by the builder is composed by the following pieces:

• class DynamicQueryRequest is composed by the following pieces:

filters: FilterOperation[];            // List of Query Request to apply.
offset: number;                        // Offset to apply to the query for pagination.
limit: number;                         // Limit to apply to the query for pagination.
order: string | Object;                // Order to apply to the query for response sorting.

• type DynamicQueryFieldType It is used to validate the request and to transform the data to the correct type. Is also necessary to declare what type of data is expected to be returned by the query.

string | string[] | number | number[] | boolean | Date | Date[];

• enum DynamicQueryAliasOperator It is used to define the operator to apply for the specified field.

OR | IN_LIST | BETWEEN | EQUAL | GRATER_THAN | GRATER_OR_EQUAL | LESS_THAN | LESS_OR_EQUAL | TEXT_SEARCH | STR_LENGTH

• class FilterOperation Is the core of the standard request. It is composed by the following pieces:

name: string;                          // Name used by the developer and it will be ignored by the builder.
field: string;                         // Collection field alias to query on.
value: DynamicQueryFieldType;          // Value or the values that have to be matched.
condition: DynamicQueryAliasOperator;  // Operator to apply to the field.;

Dictionary

• interface DictionaryInterface represents the dictionary used by the builder.

alias: string;                         // Represent the collection name.
_dictionary: DictionaryElement[];      // Map of the collection fields.

• type DictionaryElement represents the relation between the alias and the entity's field to query on.

alias: string,                         // Represents the alias of the field.
field: string | string[],              // Real name of the field
fieldType: DynamicQueryField,          // Type of the field.
customOp?: Object[],                   // Custom operations to apply to the field.
function?: DictionaryFunction,         // Function to apply to the field.
toExclude?: boolean,                   // Flag to exclude the field from the query.
dependOn?: string                      // Field that this field depends on another dictionary element.

• type DynamicQueryFieldType

string | string[] | number | number[] | boolean | Date | Date[];

• type DictionaryFunction

(values: DynamicQueryFieldType) => Object | Object[];