# A Database Query Building Approach: Device Manager Services as Examples

## Outline

- data retrieving methods (using the traditional approach)
- naming and specs
- pros & cons
- Inspirations
- query builder
- spcification pattern
- Database query building approach
- custom query builder
- Discussions

## Data Retrieving Methods (Using the Traditional Approach)

### Naming and Specs
- find and count methods (in a repository)
- one target finding methods
- .findOne-()
- many targets finding methods
- .findMany-()
- targets counting methods
- .count-()

    class TargetRepository {
      // ....

      async findOneTargetByColumnA(
        columnA: string
      ): Promise<TargetEntity> {
        const data = await targetModel.findOne({
          where: {
            columnA: { [Op.eq]: columnA },
          },
        });

        return EntityMap.toTargetEntity(data);
      }

      async findOneTargetByColumnAAndColumnB(
        columnA: string,
        columnB: number,
      ): Promise<TargetEntity> {
        const data = await targetModel.findOne({
          where: {
            columnA: { [Op.eq]: columnA },
            columnB: { [Op.gt]: columnB },
          },
        });

        return EntityMap.toTargetEntity(data);
      }

      // .... other find one methods .... //

      async findManyTargetsByColumnA(
        columnA: string[],
      ): Promise<TargetEntity[]> {
        const data = await targetModel.findAll({
          where: {
            columnA: { [Op.in]: columnA },
          },
          order,
          offset,
          limit,
        });

        return data.map(EntityMap.toTargetEntity);
      }

      async findManyTargetsByColumnAAndColumnB(
        columnA: string[],
        columnB: number,
      ): Promise<TargetEntity[]> {
        const data = await targetModel.findAll({
          where: {
            columnA: { [Op.notIn]: columnA },
            columnB: { [Op.gte]: columnB },
          },
          order,
          offset,
          limit,
        });

        return data.map(EntityMap.toTargetEntity);
      }

      // .... other find many methods .... //

      async countTargetsByColumnA(
        columnA: string[],
      ): Promise<number> {
        return targetModel.count({
          where: {
            columnA: { [Op.in]: columnA },
          },
        });
      }

      async countTargetsByColumnAAndColumnB(
        columnA: string[],
        columnB: number,
      ): Promise<number> {
        return targetModel.count({
          where: {
            columnA: { [Op.notIn]: columnA },
            columnB: { [Op.lt]: columnB },
          },
        });
      }

      // .... other count methods .... //
    }

- example
> a repository with single device finding methods, multiple devices finding methods, and device counting methods

    class GroupRepository {
      // ....

      async findOneGroupByDeviceId(
        deviceId: string,
      ): Promise<GroupEntity> {
        const data = await groupModel.findOne({
          where: {
            deviceId: { [Op.eq]: deviceId }
          },
        });

        return EntityMap.toGroupEntity(data);
      }

      async findOneGroupByEntityIdAndName(
        entityId: string,
        name: string,
      ): Promise<GroupEntity> {
        const data = await groupModel.findOne({
          where: {
            entityId: { [Op.eq]: entityId },
            name: { [Op.eq]: name },
          },
        });

        return EntityMap.toGroupEntity(data);
      }

      // .... other find one methods .... //

      async findManyGroupsByEntityId(
        entityId: string,
      ): Promise<GroupEntity[]> {
        const data = await groupModel.findAll({
          where: {
            entityId: { [Op.eq]: entityId },
          },
          order: 'createTime',
          offset: 0,
          limit: 100,
        });

        return data.map(EntityMap.toGroupEntity);
      }

      async findManyGroupsByEntityIdAndDepth(
        entityId: string,
        depth: number,
      ): Promise<GroupEntity[]> {
        const data = await groupModel.findAll({
          where: {
            entityId: { [Op.eq]: entityId },
            depth: { [Op.gte]: depth },
          },
          order: '-modifyTime',
          offset: 0,
          limit: 10,
        });

        return data.map(EntityMap.toGroupEntity);
      }

      // .... other find many methods .... //

      async countGroupsByEntityId(
        entityId: string,
      ): Promise<number> {
        return groupModel.count({
          where: {
            entityId: { [Op.eq]: entityId },
          },
        });
      }

      async countGroupsByEntityIdAndDepth(
        entityId: string,
        depth: number,
      ): Promise<number> {
        return groupModel.count({
          where: {
            entityId: { [Op.eq]: entityId },
            depth: { [Op.lt]: depth },
          },
        });
      }

      // .... other count methods .... //
    }

### Pros & Cons
- pros
- simple and intuitive syntax
- fast delivery
- cons
- duplicate or similar lines of code
- high repetition
- method naming without complete semantics
- poor understandability
- complex or trivial logic
- low readability and maintainability

## Inspirations

### Query Builder
<!-- sample code -->

- Query Builder in Laravel [ref](https://laravel.com/docs/11.x/queries)


### Specification Pattern

- Specifications in Spring Data JPA [ref](https://docs.spring.io/spring-data/jpa/reference/jpa/specifications.html)


<!-- https://docs.spring.io/spring-data/jpa/reference/jpa/specifications.html -->

## Database Query Building Approach

### Custom Query Builder

- to consider and deal with
- table columns
- table relations
- pagination

    class CustomQueryBuilder {
      toWhereOptions() WhereOptions {}

      toOrder(): Literal {}

      toLimit(): number {}

      toOffset(): number {}
    }

- to make data retrieving methods more general (widely used)
- one target finding method
- .findOne-ByQueryBuilder()
- many targets finding method
- .findMany-ByQueryBuilder()
- targets counting method
- .count-ByQueryBuilder()

    class TargetRepository {
      async findOneTargetByQueryBuilder(
        queryBuilder: CustomQueryBuilder,
      ): Promise<TargetEntity> {
        const data = await targetModel.findOne({
          where: queryBuilder.toWhereOptions(),
        });

        return EntityMap.toTargetEntity(data);
      }

      async findManyTargetsByQueryBuilder(
        queryBuilder: CustomQueryBuilder,
      ): Promise<TargetEntity[]> {
        const data = await targetModel.findAll({
          where: queryBuilder.toWhereOptions(),
          order: queryBuilder.toOrder(),
          offset: queryBuilder.toOffset(),
          limit: queryBuilder.toLimit(),
        });

        return data.map(EntityMap.toTargetEntity);
      }

      async countTargetsByQueryBuilder(
        queryBuilder: CustomQueryBuilder,
      ): Promise<number> {
        return this.targetModel.count({
          where: queryBuilder.toWhereOptions(),
        });
      }  
    }

- example
> query devices using custom query builder

    class DeviceRepository {
      async findOneDeviceByQueryBuilder(queryBuilder: DeviceQueryBuilder) {
        const data = await deviceModel.findOne({
          where: queryBuilder.toWhereOptions(),
        });

        return EntityMap.toDeviceEntity(data);
      }

      async findManyDevicesByQueryBuilder(queryBuilder: DeviceQueryBuilder) {
        const data = await deviceModel.findAll({
          where: queryBuilder.toWhereOptions(),
          order: queryBuilder.toOrder(),
          offset: queryBuilder.toOffset(),
          limit: queryBuilder.toLimit(),
        });

        return data.map(EntityMap.toDeviceEntity);
      }

      async countDevicesByQueryBuilder(queryBuilder: DeviceQueryBuilder) {
        return deviceModel.count({
          where: queryBuilder.toWhereOptions(),
        });
      }  
    }

### Table Columns in Custom Query Builder

- set criteria
- UUID string
- eq-, ne-, in-, notIn-, ....
- non-UUID string
- eq-, ne-, in-, notIn-, iLike-, ....
- number
- eq-, ne-, lt-, lte-, gt-, gte-, ....

    class CustomQueryBuilder {
      // columnA: string // UUID
      eqColumnA: string;
      neColumnA: string;
      inColumnA: string[];
      notInColumnA: string[];

      // columnB: string // non-UUID
      eqColumnB: string;
      iLikeColumnB: string;

      // columnC: number // integer, float
      eqColumnC: number;
      neColumnC: number;
      ltColumnC: number;
      lteColumnC: number;
      gtColumnC: number;
      gteColumnC: number;

      // ....
    } 

- generate predicate
- .toWhereOptions() method

    class CustomQueryBuilder {
      // ....

      toWhereOptions(): WhereOptions {
        const whereOptions: WhereOptions<ModelAttributes> = {};

        if (this.eqColumnA) {
          whereOptions.columnA = { [Op.eq]: this.eqColumnA };
        } else if (this.inColumnA) {
          whereOptions.columnA = { [Op.in]: this.inColumnA };
        }

        if (this.eqColumnB) {
          whereOptions.columnB = { [Op.eq]: this.eqName };
        } else if (this.iLikeColumnB) {
          whereOptions.columnB = { [Op.iLike]: `%${this.iLikeColumnB}%` };
        } else if (this.inColumnB) {
          whereOptions.columnB = { [Op.in]: this.inColumnB };
        }

        if (Number.isInteger(this.eqColumnC)) {
          whereOptions.columnC = { [Op.eq]: this.eqColumnC };
        } else if (this.neColumnC) {
          whereOptions.columnC = { [Op.ne]: this.neColumnC };
        } else if (this.lteColumnC) {
          whereOptions.columnC = { [Op.lte]: this.lteColumnC };
        } else if (this.gtColumnC) {
          whereOptions.columnC = { [Op.gt]: this.gtColumnC };
        }

        return whereOptions;
      }
    }

### Pagination in Custom Query Builder

- set criteria
- sort
- type: string
- sort by [column] in [ascending/descending] order
> e.g.
sort by createTime in ascending order: 'createTime'
sort by depth in descending order: '-depth'
- count
- type: number
- page size
- page
- type: number
- page index, 0-indexed

    class PaginationQueryBuilder {
      sort: string;
      count: number;
      page: number;

      // ....
    }

- generate predicate
- .toOrder() method
- .toOffset() method
- .toLimit() method

    class PaginationQueryBuilder {
      sort: string;
      count: number;
      page: number;

      toOrder(): Literal {
        if (this.sort) {
          const field = this.sort.startsWith("-")
            ? this.sort.slice(1)
            : this.sort;
          const order = this.sort.startsWith("-")
            ? "DESC"
            : "ASC";

          return literal(`${field} ${order}`);
        }
        return null;
      }

      toLimit(): number {
        const limit = this.count;
        return Number.isInteger(limit) ? limit : null;
      }

      toOffset(): number {
        const offset = this.count * this.page;
        return Number.isInteger(offset) ? offset : null;
      }
    }

- example
> [use case]
find folders of a given entity and in depth no less than 1
sort folders by depth in descending order
skip the first (10 * 2) ones
retrieve the next 10 ones

    // ....
    class PaginationQueryBuilder {
      sort: string;
      count: number;
      page: number;

      // ....
    }

    class FolderQueryBuilder extends PaginationQueryBuilder {
      eqEntityId: string;
      gteDepth: string;

      // ....

      toWhereOptions() {
        if (this.eqEntityId) {
          whereOptions.entityId = { [Op.eq]: this.eqEntityId };
        }

        if (this.gteDepth) {
          whereOptions.depth = { [Op.gte]: this.gtDepth };
        }
      }
    }

    const queryBuilder = new FolderQueryBuilder();
    queryBuilder.entityId = targetEntityId;
    queryBuilder.gteDepth = 1;
    queryBuilder.sort = '-depth';
    queryBuilder.count = 10;
    queryBuilder.page = 2;

    const folderEntities = await folderRepository.findManyFoldersByQueryBuilder(
      queryBuilder
    );

## Discussions

- Is custom query builder a MUST?
- No, use it if needed. Decisions are yours.
- Can we use traditional data retrieving approach after custom query builder is applied?
- Yes, use the traditional one when the extremely complex queries are rarely used.

<!--

https://refactoring.guru/design-patterns/builder

https://docs.spring.io/spring-data/jpa/reference/jpa/specifications.html

https://medium.com/@pawel_klimek/domain-driven-design-specification-pattern-82867540305c

https://hackmd.io/c/codimd-documentation/%2F%40codimd%2Fmarkdown-syntax

-->