ReadMany

Read Many Capabilities

Often times in a project we need to implement filters to retrieve a list of entries. This task can be quite cumbersome even using an ORM like TypeORM. Sometimes we need to filter based on a single field, sometimes we need to filter based on multiple fields. Sometimes we need to filter based on a relationship. Sometimes we need to filter based on a relationship and a field. These various combinations often lead to mulitple methods in the DAO layer and multiple endpoints in the controller layer causing issue with maintainability.

To solve this issue we can use the ReadMany capabilities provided by @smoke-trees/postgres-backend. The ReadMany capabilities allow you to

1. Filter based on a single or multiple fields
2. Provide search over a single or multiple fields
3. Provide a date filter over created date
4. Provide inbuilt pagination support
5. Provide a sort functionality

You can use all these functionalities by passing query parameters to the endpoint. The query parameters are as follows:

1. Entity Property: Use entity property like id, name, userId to filter based on a single field or multiple fields. When multiple fields are present the filter is applied as an AND operation.

2. Search: For search using field like name to search for a value in the like[name] field. When multiple fields are present the search is applied as an AND operation. This behaviour can be configured using likeBehaviour parameter. Use %value% to search for a value in the field. The % is a wildcard character.

3. Date Filter: For created date filer use fromCreatedDate and toCreatedDate to filter based on the created date. The date should be in ISO8601 format.

4. Pagination: For pagination use page and count for pagination configuration.

5. Sort: For sorting use order and orderBy to sort the results. The orderBy field should be the entity property and the order field should be ASC or DESC.

Here is a route with all the capabilities:

/address?like[name]=%John%&fromCreatedDate=2021-01-01&toCreatedDate=2021-01-31&page=1&count=10&order=ASC&orderBy=name

Customizing ReadMany

The ServiceController and DAO class provides a helper method that were used to implement the ReadMany capabilities.

The ServiceController class provides a method parseReadManyQueries that parses the query parameters giving back data that can be used to filter the entities. If you want to add custom filters you can use this method or override it to add custom filters. The method is as follows:

  parseReadManyQuery(query: ParsedQs) {
    const {
      orderBy,
      order,
      page,
      count,
      nonPaginated,
      fromCreatedDate,
      toCreatedDate,
      like,
      likeBehaviour,
      ...filter
    } = query;
    let pageNumberParsed = parseInt(page?.toString() ?? "1");
    let countParsed = parseInt(count?.toString() ?? "10");
    let fromCreatedDateDate;
    let toCreatedDateDate;

    let likeBehaviourParse: "and" | "or" | undefined = likeBehaviour
      ?.toString()
      ?.toLowerCase() as "and" | "or" | undefined;
    if (likeBehaviourParse !== "or" && likeBehaviourParse !== "and") {
      likeBehaviourParse = "and";
    }

    let orderParsed: string = order?.toString()?.toUpperCase() ?? "DESC";

    if (isNaN(pageNumberParsed)) {
      pageNumberParsed = 1;
    }
    if (isNaN(countParsed)) {
      countParsed = 10;
    }
    if (orderParsed !== "DESC" && orderParsed !== "ASC") {
      orderParsed = "DESC";
    }
    if (fromCreatedDate) {
      fromCreatedDateDate = new Date(fromCreatedDate.toString());
      if (isNaN(fromCreatedDateDate.getTime())) {
        fromCreatedDateDate = undefined;
      }
    }
    if (toCreatedDate) {
      toCreatedDateDate = new Date(toCreatedDate.toString());
      toCreatedDateDate.setDate(toCreatedDateDate.getDate() + 1);
      if (isNaN(toCreatedDateDate.getTime())) {
        toCreatedDateDate = undefined;
      }
    }
    let nonPaginatedParsed;
    if (nonPaginated === "true" && this.optionsPath.readManyWithoutPagination) {
      nonPaginatedParsed = true;
    } else {
      nonPaginatedParsed = false;
    }

    return {
      pageNumberParsed,
      countParsed,
      orderParsed: orderParsed as "ASC" | "DESC",
      orderBy: orderBy?.toString() as keyof BaseEntity,
      /* eslint-disable-next-line @typescript-eslint/no-explicit-any */
      filter: filter as any,
      likeBehaviour: likeBehaviourParse,
      fromCreatedDateDate,
      toCreatedDateDate,
      /* eslint-disable-next-line @typescript-eslint/no-explicit-any */
      like: like as any,
      nonPaginated: nonPaginatedParsed,
    };
  }

Any property that is not used by the ReadMany capabilities is returned as a filter. That includes properties of entities that are later used to filter the entities.

The DAO class provides helpers to filter the entites based on fitler, like, fromCreatedDate, toCreatedDate and likeBehaviour.

Here are the functions from DAO:

  /**
   * Parses the filter values. It parses the values like true and false which
   * comes from query params as string to boolean and also converts the array
   * based filters to In
   */
  parseFilter(
    where: FindOptionsWhere<Entity> | FindOptionsWhere<Entity>[]
  ): FindOptionsWhere<Entity> | FindOptionsWhere<Entity>[] {
    if (where instanceof Array) {
      where = where.map((it) =>
        this.parseFilter(it)
      ) as FindOptionsWhere<Entity>[];
      return where;
    }
    Object.keys(where).forEach((key) => {
      if ((where as any)[key] instanceof Array) {
        (where as any)[key] = In((where as any)[key]);
      }
      if ((where as any)[key] === "true") {
        (where as any)[key] = true;
      }
      if ((where as any)[key] === "false") {
        (where as any)[key] = false;
      }
    });
    return where;
  }

  /**
   * Parses sort values. Sort object to work across relations need to have
   * syntax: { entity: {field: ASC }}
   *
   */
  parseForSort(field: keyof Entity, order: "ASC" | "DESC") {
    const result: any = {};
    let level = result;
    const sortLevels = field.toString().split(".") || [];

    for (let i = sortLevels.length - 1; i >= 0; i--) {
      const prop = sortLevels[i];
      if (i === sortLevels.length - 1) {
        level[prop] = order || "ASC";
      } else {
        const newLevel = { [prop]: level };
        level = newLevel;
      }
    }
    return level;
  }

  /**
   * Parses the like values. It parses the values to ILike for case insensitive
   * search. It takes a likeBehaviour which can be 'and' or 'or'. This dictates
   * how the like values are combined. If 'and' is passed, all the values are
   * combined with AND. If 'or' is passed, all the values are combined with OR.
   */
  parseForLike(
    like?: { [key: string]: string },
    likeBehaviour: "and" | "or" = "and"
  ) {
    let parsedLike: { [key: string]: string } | { [key: string]: string }[];

    if (like && likeBehaviour === "and") {
      parsedLike = Object.keys(like).reduce((acc, it) => {
        acc[it] = ILike((like as any)[it] as string);
        return acc;
      }, {} as any);
    } else if (like && likeBehaviour === "or") {
      parsedLike = Object.keys(like).map((it) => ({
        [it]: ILike((like as any)[it]),
      })) as any;
      if ((parsedLike?.length ?? 0) === 0) {
        parsedLike = {};
      }
    } else {
      parsedLike = {};
    }

    return parsedLike;
  }

  /**
   * Parses the date values. It parses the values to MoreThanOrEqual AND
   * LessThanOrEqual for date range search. It takes a from and to 2024-03-21
   * values. If both are passed, it combines them with Between. If only fromCreatedDate
   * is passed, it uses MoreThanOrEqual. If only toCreatedDate is passed, it uses
   * LessThanOrEqual.
   */
  parseForDates(
    field: keyof Entity,
    from?: Date | string,
    to?: Date | string,
    where?: FindOptionsWhere<Entity> | FindOptionsWhere<Entity>[]
  ) {
    if (from && to && !(where instanceof Array)) {
      if (!where) {
        where = {};
      }
      where = { ...where, [field]: Between(from, to) };
    } else if (from && !(where instanceof Array)) {
      if (!where) {
        where = {};
      }
      where = { ...where, createdAt: MoreThanOrEqual(from) };
    } else if (to && !(where instanceof Array)) {
      if (!where) {
        where = {};
      }
      where = { ...where, createdAt: LessThanOrEqual(to) };
    }
    return where;
  }

  /**
   * Parses the where values. It combines all the values like where, like, fromCreatedDate
   * and toCreatedDate to a single where object. It also takes a likeBehaviour which can
   * be 'and' or 'or'. This dictates how the like values are combined. If 'and' is passed,
   * all the values are combined with AND. If 'or' is passed, all the values are combined
   * with OR.
   */
  parseWhere(
    where?: FindOptionsWhere<Entity> | FindOptionsWhere<Entity>[],
    like?: { [key: string]: string },
    likeBehaviour?: "and" | "or",
    fromCreatedDate?: Date,
    toCreatedDate?: Date
  ) {
    if (where) {
      where = this.parseFilter(where);
    }

    where = this.parseForDates(
      "createdAt",
      fromCreatedDate,
      toCreatedDate,
      where
    );

    const parsedLike = this.parseForLike(like, likeBehaviour);

    where = this.mergeParseLikeWhere(parsedLike, like, likeBehaviour, where);

    return where;
  }

  /**
   * Merges the parsed like values with the where values. It takes a parsedLike
   * which is the parsed like values, like which is the original like values, likeBehaviour
   * which can be 'and' or 'or'.
   */
  mergeParseLikeWhere(
    parsedLike: { [key: string]: string } | { [key: string]: string }[],
    like?: { [key: string]: string },
    likeBehaviour?: "and" | "or",
    where: FindOptionsWhere<Entity> | FindOptionsWhere<Entity>[] = {}
  ) {
    if (likeBehaviour === "and") {
      where = { ...where, ...(parsedLike as any) };
    } else if (likeBehaviour === "or" && parsedLike instanceof Array) {
      where = parsedLike.map((a) => {
        return { ...where, ...a };
      }) as FindOptionsWhere<Entity>[];
    } else {
      where = { ...where, ...(parsedLike as any) };
    }
    return where;
  }

The parseFilter method is used to parse the filter values. It parses the values like true and false which comes from query params as string to boolean and also converts the array based filters to In.

The parseForSort method is used to parse sort values. Sort object to work across relations need to have syntax: { entity: {field: ASC }}.

The parseForLike method is used to parse the like values. It parses the values to ILike for case insensitive search. It takes a likeBehaviour which can be 'and' or 'or'. This dictates how the like values are combined. If 'and' is passed, allow the values are combined with AND. If 'or' is passed, all the values are combined with OR.

The parseForDates method is used to parse the date values. It parses the values to MoreThanOrEqual AND LessThanOrEqual for date range search. It takes a from and to to values. If both are passed, it combines them with Between. If only fromCreatedDate is passed, it uses MoreThanOrEqual. If only toCreatedDate is passed, it uses LessThanOrEqual. You can use this to filter based on date values

The parseWhere method is used to parse the where values. It combines all the values like where, like, fromCreatedDate and toCreatedDate to a single where object. It also takes a likeBehaviour which can be 'and' or 'or'. This dictates how the like values are combined. If 'and' is passed, all the values are combined with AND. If 'or' is passed, all the values are combined with OR.

The mergeParseLikeWhere method is used to merge the parsed like values with the parseWhere values. It takes a parsedLike which is the parsed like values, like which is the original like values, likeBehaviour which can be 'and' or 'or'.

You can use these methods to customize the ReadMany capabilities to add custom filters to the entities.