如何在TypeORM中左连接时过滤关联表并保留主表全部记录

需求说明 #

实体A与B是一对多关联，B拥有可空属性p。需要在保留所有A记录的前提下，仅关联B中p为null的数据，得到类似如下的查询结果：

或TypeORM解析后的实体结构：

[
  { id: 'A1', Bs:[] },
  { 
    id: 'A2', 
    Bs:[ 
      { id: 'B1', p: undefined }, 
      { id: 'B2', p: undefined } 
    ] 
  },
  { id: 'A3', Bs:[] }
]

已知可通过TypeScript二次过滤实现，这里提供SQL和TypeORM直接实现的方案：

SQL直接实现 #

核心是把b.p IS NULL的条件放在LEFT JOIN的ON子句中，而非WHERE子句（WHERE会过滤掉没有符合条件B的A记录）。示例SQL如下：

SELECT a.id AS A_id, b.id AS B_id, b.p AS B_p
FROM A a
LEFT JOIN B b ON a.id = b.a_id AND b.p IS NULL;

TypeORM直接实现 #

有两种常用方式：

1. 使用QueryBuilder动态设置关联条件 #

通过leftJoinAndSelect的第三个参数指定关联时的过滤条件，示例代码：

import { getRepository } from "typeorm";
import { A } from "./entities/A";

const queryResult = await getRepository(A)
  .createQueryBuilder('a')
  // 第三个参数是关联B时的过滤条件
  .leftJoinAndSelect('a.Bs', 'b', 'b.p IS NULL')
  .getMany();

查询返回的A实体中，Bs数组只会包含p为null的B记录，无符合条件的则为空数组，完全匹配需求结构。

2. 在实体关联定义中默认添加过滤条件 #

如果希望每次查询A时默认只加载p为null的B，可以在@OneToMany装饰器中配置where选项：

import { Entity, PrimaryGeneratedColumn, OneToMany } from "typeorm";
import { B } from "./B";

@Entity()
export class A {
  @PrimaryGeneratedColumn()
  id: string;

  @OneToMany(() => B, b => b.a, {
    where: { p: null }
  })
  Bs: B[];
}

注意：这种方式是全局生效的，如果有需要加载所有关联B的场景，需要单独用QueryBuilder覆盖该条件。

内容的提问来源于stack exchange，提问作者Charl