如何使用TypeORM QueryBuilder(关系的关系的关系)进行子查询连接

Question

我想转换下面的SQL查询(已测试&工作)，到一个TypeORM查询与QueryBuilder，但我不能弄清楚如何(不断得到错误，user_roles_role没有加入):

select "invite"."code", "invite"."expired", "invite"."sent", "role"."name" as "user_role"
from "invite"
left join "user_roles_role"
on "user_roles_role"."user_id"="invite"."user_id"
left join "role"
on "role"."_id"="user_roles_role"."role_id"
where "invite"."code"=$1
and "role"."name"='ADMIN';

Answer 1

多亏了这篇文章，我终于弄明白了:https://dev.to/yoshi_yoshi/typeorm-query-builder-with-subquery-490c

下面是我的SQL代码的QueryBuilder代码:

const invites = await this.connection
.getRepository(Invite)
.createQueryBuilder('invite')
.select(['invite.code', 'invite.sent', 'invite.expired', 'role.name'])
.leftJoinAndSelect('invite.user', 'user')
.leftJoinAndSelect('user.roles', 'role')
.where('invite.code = :id', { id: code })
.andWhere('role.name = :name', { name: 'ADMIN' })
.getOne()