如何将原始SQL查询转换为TypeORM查询生成器?

Question

如何将子查询选择与不同的类型转换为TypeORM查询生成器?谢谢。

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

您可以首先构建嵌套查询，然后将其左连接到主查询:

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();

其中A、B、C为TypeORM实体。