TypeORM关联查询仅返回匹配产品，如何保留用户全部关联产品？

实体定义 #

User 实体 #

@Entity('user')
export class User {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @ManyToMany(() => Product, { onDelete: 'NO ACTION', onUpdate: 'CASCADE' })
  @JoinTable({
    name: 'user-products',
    joinColumn: { name: 'userId', referencedColumnName: 'id' },
    inverseJoinColumn: { name: 'productId', referencedColumnName: 'id' },
  })
  products?: Product[];
}

Product 实体 #

@Entity({ name: 'product' })
@Unique(['name'])
export class Product {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({ type: 'varchar' })
  name: string;

  @ManyToMany(() => User, user => user.products, { onDelete: 'NO ACTION', onUpdate: 'CASCADE' })
  users?: User[];
}

UserProducts 中间表实体 #

@Entity('user-products')
export class UserProducts {
  @PrimaryColumn('uuid', { name: 'userId' })
  userId: string;

  @PrimaryColumn('uuid', { name: 'productId' })
  productId: string;

  @ManyToOne(() => User, user => user.products, { onDelete: 'NO ACTION', onUpdate: 'CASCADE' })
  @JoinColumn([{ name: 'userId', referencedColumnName: 'id' }])
  users: User[];

  @ManyToOne(() => Product /*, product => product.users*/, { onDelete: 'NO ACTION', onUpdate: 'CASCADE' })
  @JoinColumn([{ name: 'productId', referencedColumnName: 'id' }])
  products: Product[];
}

当前问题 #

基础查询可正常返回用户及其所有关联产品：

this.myRepo
      .createQueryBuilder('user')
      .leftJoinAndSelect('user.products', 'userProducts')

但添加产品名称模糊搜索后，返回的用户仅包含匹配搜索条件的产品（例如搜索"product_2"时，用户仅显示该产品，其他关联产品丢失）：

this.userRepo
      .createQueryBuilder('user')
      .leftJoinAndSelect('user.products', 'userProducts')
      .where(`userProducts.name ilike '%${mySearchValue}%'`)

需求：返回所有关联过匹配产品的用户，且每个用户保留其全部关联产品。

解决方案 #

问题根源是直接对关联表加WHERE条件会过滤掉不匹配的产品记录，同时截断用户的关联结果集。需先筛选符合条件的用户，再独立加载其所有产品，以下是三种可行方案：

方法1：子查询筛选用户ID #

this.userRepo
  .createQueryBuilder('user')
  .leftJoinAndSelect('user.products', 'products') // 加载用户所有产品
  .where('user.id IN (:...userIds)', {
    userIds: await this.userRepo
      .createQueryBuilder('u')
      .select('u.id')
      .innerJoin('u.products', 'p')
      .where('p.name ilike :searchValue', { searchValue: `%${mySearchValue}%` })
      .getRawMany()
      .then(rows => rows.map(row => row.u_id))
  })

方法2：JOIN+GROUP BY筛选用户 #

this.userRepo
  .createQueryBuilder('user')
  .leftJoinAndSelect('user.products', 'products')
  .innerJoin('user.products', 'filteredProducts')
  .where('filteredProducts.name ilike :searchValue', { searchValue: `%${mySearchValue}%` })
  .groupBy('user.id')
  .addGroupBy('products.id') // 按ORM要求添加产品分组，确保所有产品被加载

方法3：Exists子查询（性能更优） #

this.userRepo
  .createQueryBuilder('user')
  .leftJoinAndSelect('user.products', 'products')
  .whereExists(qb => qb
    .select('1')
    .from('product', 'p')
    .innerJoin('user-products', 'up', 'up.productId = p.id')
    .where('up.userId = user.id')
    .andWhere('p.name ilike :searchValue', { searchValue: `%${mySearchValue}%` })
  )

关键说明 #

• 核心逻辑：先筛选出关联过匹配产品的用户，再独立加载这些用户的所有关联产品，而非直接过滤产品后返回。
• 避免直接在leftJoinAndSelect的关联表上加WHERE条件，否则会将左连接转为内连接效果，同时丢失用户的非匹配产品。
• 使用参数绑定（:searchValue）代替字符串拼接，防止SQL注入风险。

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