TypeORM : QueryBuilder | raw query

Null값을 포함한 평균값 구하기

Use coalesce() to return the real value of zero for null columns:

COALESCE(AVG(job.totalWorkTime)::INTEGER, 0)

SQL: AVG with NULL Values

배열의 길이 구하기

jsonb_array_length

COALESCE(AVG(jsonb_array_length((job.result->>'labels')::JSONB))::FLOAT, 0)

float는 소수점 7자리까지 표현
double와 float의 차이점

조건에 따라서 값을 지정해 주는 CASE문

CASE문 형식

CASE WHEN 조건1 THEN 값1 
WHEN 조건2 THEN 값2 
ELSE 값3 END

컬럼이 조건1 일 때는 값1
조건 2일 때는 값2를 반환하고 조건에 맞지 않는 경우에는 값3을 반환
MSSQL CASE 문 . 조건에 따라 값 정하기 ! CASE WHEN THEN

Where in query

1. whereInIds(Query Builder)

await this.userRepository.createQueryBuilder()
          .whereInIds(request.ids)
          .getMany()
}

2. raw query

createQueryBuilder("user")
    .where("user.id IN (:...ids)", { ids: [1, 2, 3, 4] })

typeorm을 이용한 where in 쿼리 사용하기

leftJoinAndMapMany

LEFT JOINs entity's table, SELECTs the data returned by a join and MAPs all that data to some entity's property. This is extremely useful when you want to select some data and map it to some virtual property. It will assume that there are multiple rows of selecting data, and mapped result will be an array. You also need to specify an alias of the joined data. Optionally, you can add condition and parameters used in condition.

leftJoinAndMapMany(mapToProperty: string, 
                   entity: string | Function, 
                   alias: string, 
                   condition?: string | undefined,
                   parameters?: ObjectLiteral | undefined)

Entity를(이때 관계가 있어야 한다) left join하고 raw data를 select한다. 그리고 그 데이터들을 mapping한다. 데이터를 셀렉하고 배열로 나타내고 싶을 때 사용할 수 있다.

await this.usersRepository
      .createQueryBuilder('user')
      .select('user')
      .leftJoinAndMapMany(
        'user.assignments', // mapping할 property
        Assignment, // join할 entity
        'assignment', // Column alias
        'user.id = assignment.userId AND assignment.subtaskId = :subtaskId', // where condition
        { // where문에 쓴 parameter
          subtaskId,
        }, 
      )
      .whereInIds(assignedUserIds);

query: SELECT COUNT(DISTINCT("user"."id")) AS "cnt" 
FROM "Users" "user" 
LEFT JOIN "Assignments" "assignment" 
ON  "user"."id" = "assignment"."userId" 
AND "assignment"."subtaskId" = $1 
AND "assignment"."deletedAt" IS NULL 
WHERE ( "user"."id" IN ($2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25) ) 
AND ( "user"."deletedAt" IS NULL )

result

[
  User {
  	id:
  	name:
  },
  User {
  	id:
  	name:
  },
   ...
    
]

new Brackets(qb)

복잡한 where문을 쓰고 싶다면 new Brackets을 사용할 수 있다.

createQueryBuilder("user")
    .where("user.registered = :registered", { registered: true })
    .andWhere(new Brackets(qb => {
        qb.where("user.firstName = :firstName", { firstName: "Timber" })
          .orWhere("user.lastName = :lastName", { lastName: "Saw" })
    }))

    if (nameOrEmail) {
      baseQuery.andWhere(
        new Brackets((qb) => {
          qb.where(`user.name LIKE :nameOrEmail`, {
            nameOrEmail: `%${nameOrEmail}%`,
          }).orWhere(`email LIKE :nameOrEmail`, {
            nameOrEmail: `%${nameOrEmail}%`,
          });
        }),
      );

Adding WHERE expression

Like operators

TypeORM provides a lot of built-in operators that can be used to create more complex comparisons:

import { Like } from "typeorm"

const loadedPosts = await dataSource.getRepository(Post).findBy({
    title: Like("%out #%"),
})

How to perform a like query TypeORM
typeORM operators

LeftJoin

1. leftJoin(property, alias, condition?, parameters?)

LEFT JOINs (without selection) entity's property. Given entity property should be a relation. You also need to specify an alias of the joined data. Optionally, you can add condition and parameters used in condition.

(method) SelectQueryBuilder<Profit>
  .leftJoin(property: string,
            alias: string,
            condition?: string | undefined,
            parameters?: ObjectLiteral | undefined)
  : SelectQueryBuilder<Profit> (+3 overloads)

e.g.

  .createQueryBuilder('profit')
  .leftJoin('profit.user', 'user') /

또는

  .createQueryBuilder('profit')
  .leftJoin('profit.user', 'user') // 또는
  .leftJoin('profit.user', 'user', 'user.id = :userIds', { userIds }) 

이렇게 사용할 수 있다.

2. leftJoin(qb, alias, condition?, parameters?)

LEFT JOINs (without selection) given subquery. You also need to specify an alias of the joined data. Optionally, you can add condition and parameters used in condition.

(method)SelectQueryBuilder<User>
  .leftJoin(subQueryFactory: (qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>,
    alias: string, 
    condition?: string | undefined, 
    parameters?: ObjectLiteral | undefined)
    : SelectQueryBuilder<...> (+3 overloads)

하위 쿼리가 제공되어 콜백함수를 사용할 수 있다.

e.g.

baseQuery
      .addSelect('job_count.count', 'count')
      .leftJoin(
        (qb) => {
          if (role === UserRole.WORKER) {
            return this.jobsStatsService.appendGroupUsersByJobCountQuery({
              queryBuilder: qb,
              subtaskId,
              order,
            });
          } else {
            return this.jobsStatsService.appendGroupReviewersByJobCountQuery({
              queryBuilder: qb,
              subtaskId,
              order,
            });
          }
        },
        'job_count',
        'job_count."userId" = user.id',
      )