如何用TypeORM的createQueryBuilder关联User与School表（simple-array类型）

问题背景 #

数据库中有User和School两张表，实体定义如下：

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column({ type: 'simple-array' })
  schoolIds: number[];
}

@Entity()
export class School {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column()
  address: string;
}

当前通过两次查询再做映射可实现关联，但希望用TypeORM的createQueryBuilder通过单条查询完成关联，获取每个用户对应的关联学校。

解决方案 #

可以借助PostgreSQL的数组操作函数，结合TypeORM查询构建器实现单查询关联，核心是利用ANY操作符匹配数组元素，再通过ARRAY_AGG聚合关联的学校数据：

// 执行单条查询获取关联数据
const rawResults = await getConnection()
  .createQueryBuilder()
  .select('user.id', 'userId')
  .addSelect('user.name', 'userName')
  .addSelect('ARRAY_AGG(school.*)', 'schools')
  .from(User, 'user')
  // 用ANY匹配school.id是否存在于user的schoolIds数组中
  .leftJoin(School, 'school', 'school.id = ANY(user."schoolIds")')
  .groupBy('user.id, user.name')
  .getRawMany();

// 转换为符合业务需求的结构
const usersWithSchools = rawResults.map(item => ({
  id: item.userId,
  name: item.userName,
  // 过滤无关联学校时的null值
  schools: item.schools.filter(school => school !== null)
}));

关键说明 #

• ANY(user."schoolIds")：PostgreSQL中用于判断school.id是否存在于user.schoolIds数组中，适配TypeORM的simple-array类型存储
• ARRAY_AGG(school.*)：将每个用户关联的所有学校聚合为数组，与用户维度一一对应
• leftJoin确保即使用户没有关联学校也能被查询到，避免数据丢失

内容的提问来源于stack exchange，提问作者Đoàn Đức Bảo