adding where clause on relation table nestjs pgsql typeorm

Question

I have find query like this

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 };
  }

i want to search applicantInfo.applicantNumber but it is showing

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

in my enquiry entity

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;

and applicant entity

export class ApplicantEntity extends CommonEntity {

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

is it possible or i am missing something?

Answer 1

Recently I also had this same problem, looking at the TypeORM documentation I identified that this filter has to be done according to this example:

this.repository.find({
  where: {
    relation: {
      column: 'value'
    }
  }
}

Change this part of your code:

where["applicantInfo.applicantNumber"] = iLike;

By this code:

where['applicantInfo'] = { applicantNumber: iLike };

This change will certainly work in your project.