nestjs pgsql where condition with OR and AND nodejs typeorm

Question

in the following query i want to search on both name and agreementNumber but in OR mode, if name or agreementNumber matches then find but it is not working like this

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

    let where: FindOptionsWhere<AgreementEntity> = {};
    if (HelperService.isValueValid(filter.id)) {
      where["id"] = filter.id;
    }

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

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

    if (HelperService.isValueValid(filter.searchText)) {
      const iLike = ILike(`%${filter.searchText}%`);
      // where["name"] = iLike;
      // where["agreementNumber"] = iLike;

      // Create an OR condition for name and agreementNumber fields
      where['_OR'] = [
        { 'name': iLike },
        { 'agreementNumber': iLike }
      ];
    }

    const query = {
      where: where,
      relations: ["enquiryInfo", "enquiryInfo.applicantInfo"],
      order: { id: SortOrder.Desc },
      take,
      skip,
    };

    let [result, total] = await this.agreementsRepo.findAndCount(query);
    return { data: result, count: total };
  }

Answer 1

Recently I also had this same problem, looking at the TypeORM documentation I identified that there is no _OR inside a where object, so that the OR and AND conditions work, I used it as shown in the code below:

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

  const conditionWithAnd = {};
  const conditionWithOr = [];

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

  if (HelperService.isValueValid(filter.enquiryId)) {
    conditionWithAnd["enquiryId"] = filter.enquiryId;
  }

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

  if (HelperService.isValueValid(filter.searchText)) {
    const iLike = ILike(`%${filter.searchText}%`);
    conditionWithOr.push({ ...conditionWithAnd, name: iLike });
    conditionWithOr.push({ ...conditionWithAnd, agreementNumber: iLike });
  }

  const where = conditionWithOr.length ? conditionWithOr : conditionWithAnd;

  const query = {
    where,
    relations: ["enquiryInfo", "enquiryInfo.applicantInfo"],
    order: { id: SortOrder.Desc },
    take,
    skip,
  };

  let [result, total] = await this.agreementsRepo.findAndCount(query);
  return { data: result, count: total };
}

I believe this change to your code should now work correctly.