How to translate an raw SQL query to TypeORM query builder?

Question

How would I convert the subQuery select with distinct to TypeORM querybuilder? Thanks.

SELECT `time`, (case when `start` is NULL then 0 else 1 end) `is_reserved` FROM a left join (SELECT `time` FROM b join c on c.b_id = b.id WHERE b.date = '2022-04-20') MySchedule on MySchedule.time = a.time where a.is_use = 1

a table
time
is_use 

b table
id
date

c table
b_id
time

Answer 1

You can first build the nested query and then left-join it to the main query:

import { getConnection } from 'typeorm';

// build the nested query
const nestedQuery = getConnection()
  .createQueryBuilder(B, 'b').select('time')
  .innerJoin(C, 'c.b_id = b.id')
  .where(`b.date = '2022-04-20'`);

getConnection().createQueryBuilder(A, 'a')
  .select([
    'time',
  ])
  .addSelect('(case when `start` is NULL then 0 else 1 end)', 'is_reserved')

  // left join the nested query
  .leftJoin(
    `(${nestedQuery.getQuery()})`,
    'MySchedule',
    'MySchedule.time = a.time')
  .where('a.is_use = 1')
  .getMany();

Where A, B and C are TypeORM entities.