NestJS+TypeORM+PostgreSQL关联表WHERE子句查询报错问题

在基于NestJS、TypeORM和PostgreSQL的项目中，编写了如下findAllEnquiries查询方法：

async findAllEnquiries(filter: DataFilterDto): Promise<ApiResponseDto<EnquiryEntity[]>> {
    const take = filter.count || 10;
    const page = filter.pageNo > 0 ? filter.pageNo - 1 : 0;
    const skip = page * take;

    const where = {};

    if (HelperService.isValueValid(filter.id)) {
      where["id"] = filter.id;
    }

    if (HelperService.isValueValid(filter.searchText)) {
      const iLike = ILike(`%${filter.searchText}%`);
      where["remarks"] = iLike;
      where["applicantInfo.applicantNumber"] = iLike;
    }

    if (HelperService.isValueValid(filter.applicantId)) {
      where["applicantId"] = filter.applicantId;
    }

    if (HelperService.isValueValid(filter.status)) {
      where["status"] = filter.status;
    }

    console.log("EnquiriesService ~ findAllEnquiries ~ where:", where);
    let [result, total] = await this.enquiryRepo.findAndCount({
      where,
      relations: ['applicantInfo'],
      order: { createdAt: -1 },
      take,
      skip
    });

    return { data: result, count: total };
  }

尝试通过关联实体applicantInfo的applicantNumber字段搜索时，出现错误：

Property "applicantInfo.applicantNumber" was not found in "EnquiryEntity". Make sure your query is correct.

EnquiryEntity和ApplicantEntity定义如下：
EnquiryEntity：

export class EnquiryEntity extends CommonEntity {

  @Column({ name: "applicant_id", type: "integer", select: false })
  applicantId: number;

  @ManyToOne(() => ApplicantEntity) // Establish Many-to-One relationship
  @JoinColumn({ name: 'applicant_id', referencedColumnName: 'id' }) // Specify the join column
  applicantInfo: ApplicantEntity;
}

ApplicantEntity：

export class ApplicantEntity extends CommonEntity {

  @Column({ name: "applicant_no", type: "varchar" })
  applicantNumber: string;
}

请问该需求是否可行，或是我遗漏了什么配置？

这个需求完全可行，问题出在TypeORM的where条件写法上：直接用"applicantInfo.applicantNumber"这种点路径在简单where对象里不被支持，需要用以下两种方式解决：

方式一：使用嵌套where对象（TypeORM >=0.3.x支持） #

TypeORM 0.3及以上版本允许在where中用嵌套对象指定关联实体的过滤条件，修改搜索部分代码即可：

if (HelperService.isValueValid(filter.searchText)) {
  const iLike = ILike(`%${filter.searchText}%`);
  where["remarks"] = iLike;
  // 替换点路径为嵌套对象
  where["applicantInfo"] = {
    applicantNumber: iLike
  };
}

注意：如果需要同时对关联实体多个字段搜索，或者和当前实体字段用OR逻辑，这种方式灵活性有限，更适合用QueryBuilder。

方式二：使用QueryBuilder（兼容所有版本，推荐生产环境使用） #

QueryBuilder是TypeORM处理复杂查询的标准方案，能完美支持关联实体的条件过滤，还能轻松处理OR、多关联字段等复杂逻辑：

async findAllEnquiries(filter: DataFilterDto): Promise<ApiResponseDto<EnquiryEntity[]>> {
  const take = filter.count || 10;
  const page = filter.pageNo > 0 ? filter.pageNo - 1 : 0;
  const skip = page * take;

  // 初始化QueryBuilder并关联applicantInfo
  const queryBuilder = this.enquiryRepo
    .createQueryBuilder('enquiry')
    .leftJoinAndSelect('enquiry.applicantInfo', 'applicantInfo');

  // 添加id过滤
  if (HelperService.isValueValid(filter.id)) {
    queryBuilder.andWhere('enquiry.id = :id', { id: filter.id });
  }

  // 添加搜索文本过滤（支持当前实体和关联实体字段的OR逻辑）
  if (HelperService.isValueValid(filter.searchText)) {
    const searchText = `%${filter.searchText}%`;
    queryBuilder.andWhere(
      'enquiry.remarks ILIKE :searchText OR applicantInfo.applicantNumber ILIKE :searchText',
      { searchText }
    );
  }

  // 添加applicantId过滤
  if (HelperService.isValueValid(filter.applicantId)) {
    queryBuilder.andWhere('enquiry.applicantId = :applicantId', { applicantId: filter.applicantId });
  }

  // 添加status过滤
  if (HelperService.isValueValid(filter.status)) {
    queryBuilder.andWhere('enquiry.status = :status', { status: filter.status });
  }

  // 设置排序、分页
  queryBuilder.orderBy('enquiry.createdAt', 'DESC')
    .take(take)
    .skip(skip);

  // 执行查询并获取总数
  const [result, total] = await queryBuilder.getManyAndCount();

  return { data: result, count: total };
}

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