A. TypeOrm Query Builder

terminal

npx nest g module queryBuilder
npx nest g service queryBuilder
npx nest g controller queryBuilder

Module

query-builder.module.ts

import { Module } from '@nestjs/common';
import { QueryBuilderService } from './query-builder.service';
import { QueryBuilderController } from './query-builder.controller';
import { TypeOrmModule } from '@nestjs/typeorm';
import { User } from 'src/app/auth/auth.entity';

@Module({
  imports: [TypeOrmModule.forFeature([User])],
  providers: [QueryBuilderService],
  controllers: [QueryBuilderController],
})
export class QueryBuilderModule {}

Controller

query-builder.controller.ts

import { Controller, Get, Post, UseGuards } from '@nestjs/common';
import { JwtGuard } from 'src/app/auth/auth.guard';
import { QueryBuilderService } from './query-builder.service';

// @UseGuards(JwtGuard)
@Controller('query-builder')
export class QueryBuilderController {
  constructor(private readonly querybuilderService: QueryBuilderService) {}
  @Get('/latihan')
  async LatihanController() {
    return this.querybuilderService.latihan();
  }
}

Service

query-builder.service.ts

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { User } from 'src/app/auth/auth.entity';
import BaseResponse from 'src/utils/response/base.response';
import { Repository } from 'typeorm';

@Injectable()
export class QueryBuilderService extends BaseResponse {
  constructor(
    @InjectRepository(User)
    private readonly userRepository: Repository<User>,
  ) {
    super();
  }

  async latihan() {
    const result = await this.userRepository
      .createQueryBuilder('user')

      .getMany();

    return this._success('ok', result);
  }
}

QueryBuilder DTO

query-builder.dto.ts

import { IsOptional, IsString } from 'class-validator';
import { PageRequestDto } from 'src/utils/dto/page.dto';

export class latihanQueryBuilderDto extends PageRequestDto {
  @IsString()
  @IsOptional()
  nama: string;

  @IsString()
  @IsOptional()
  nama_produk: string;

  @IsString()
  @IsOptional()
  stok: string;
}

Where

query-builder.service.ts

...

  async latihan(query: latihanQueryBuilderDto) {
    const { nama } = query;
    const queryBuilder = await this.userRepository.createQueryBuilder('user');

    if (nama) {
      queryBuilder.where(`user.nama LIKE :nama`, {
        nama: `%${nama}%`,
      });
    }

    const result = await queryBuilder.getMany();

    return this._success('ok', result);
  }

Latihan query pada Produk

Module

query-builder.module.ts

import { Module } from '@nestjs/common';
import { QueryBuilderService } from './query-builder.service';
import { QueryBuilderController } from './query-builder.controller';
import { TypeOrmModule } from '@nestjs/typeorm';
import { User } from 'src/app/auth/auth.entity';
import { Produk } from 'src/app/produk/produk.entity';

@Module({
  imports: [TypeOrmModule.forFeature([User, Produk])],
  providers: [QueryBuilderService],
  controllers: [QueryBuilderController],
})
export class QueryBuilderModule {}

Controller

query-builder.controller.ts

 @Get('/latihan2')
  async LatihanController2(@Pagination() query: latihanQueryBuilderDto) {
    return this.querybuilderService.latihan2(query);
  }

Service

AndWere dan Select

query-builder.service.ts

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { User } from 'src/app/auth/auth.entity';
import BaseResponse from 'src/utils/response/base.response';
import { Repository } from 'typeorm';
import { latihanQueryBuilderDto } from './query-builder.dto';
import { Produk } from 'src/app/produk/produk.entity';

@Injectable()
export class QueryBuilderService extends BaseResponse {
  constructor(
    @InjectRepository(User)
    private readonly userRepository: Repository<User>,
    @InjectRepository(Produk)
    private readonly produkRepository: Repository<Produk>,
  ) {
    super();
  }


  async latihan2(query: latihanQueryBuilderDto) {
    const { nama_produk, stok } = query;
    const queryBuilder = await this.produkRepository.createQueryBuilder(
      'produk',
    );

    if (nama_produk) {
      queryBuilder.where(`produk.nama_produk LIKE :nama_produk`, {
        nama_produk: `%${nama_produk}%`,
      });
    }

    if (stok) {
      queryBuilder.andWhere(`produk.stok LIKE :stok`, {
        stok: stok,
      });
    }

    queryBuilder.select([
      'produk.nama_produk',
      'produk.stok',
      'produk.harga',
      'produk.deskripsi_produk',
    ]);

    const result = await queryBuilder.getMany();

    return this._success('ok', result);
  }
}

LeftJoin

query-builder.service.ts

async latihan2(query: latihanQueryBuilderDto) {
    const { nama_produk, stok } = query;
    const queryBuilder = await this.produkRepository.createQueryBuilder(
      'produk',
    );

    if (nama_produk) {
      queryBuilder.where(`produk.nama_produk LIKE :nama_produk`, {
        nama_produk: `%${nama_produk}%`,
      });
    }

    if (stok) {
      queryBuilder.andWhere(`produk.stok LIKE :stok`, {
        stok: stok,
      });
    }

    queryBuilder
      .leftJoin(`produk.created_by`, 'created_by')
      .leftJoin(`produk.updated_by`, 'updated_by')
      .select([
         'produk.id',
        'produk.nama_produk',
        'produk.stok',
        'produk.harga',
        'produk.deskripsi_produk',
        'created_by.nama',
        'updated_by.nama',
      ]);

    const result = await queryBuilder.getMany();

    return this._success('ok', result);
  }

Pagination

query-builder.service.ts

async latihan2(query: latihanQueryBuilderDto) {
    const { nama_produk, stok, limit, page, pageSize } = query;
    const queryBuilder = await this.produkRepository.createQueryBuilder(
      'produk',
    );

    if (nama_produk) {
      queryBuilder.where(`produk.nama_produk LIKE :nama_produk`, {
        nama_produk: `%${nama_produk}%`,
      });
    }

    if (stok) {
      queryBuilder.andWhere(`produk.stok LIKE :stok`, {
        stok: stok,
      });
    }

    queryBuilder
      .leftJoin(`produk.created_by`, 'created_by')
      .leftJoin(`produk.updated_by`, 'updated_by')
      .select([
        'produk.id',
        'produk.nama_produk',
        'produk.stok',
        'produk.harga',
        'produk.deskripsi_produk',
        'created_by.nama',
        'updated_by.nama',
      ])

      .skip(limit)
      .take(pageSize);

    const result = await queryBuilder.getMany();
    const total = await queryBuilder.getCount();

    return this._pagination('OK', result, total, page, pageSize);
  }