skip to Main Content

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

2

Answers


  1. Try modifying the section where you create the OR condition as follows:

    // ...
    
    if (HelperService.isValueValid(filter.searchText)) {
      const iLike = ILike(`%${filter.searchText}%`);
    
      // Create an OR condition for name and agreementNumber fields
      where['OR'] = [
        { 'name': iLike },
        { 'agreementNumber': iLike }
      ];
    }
    
    // ...
    

    Make sure to replace ‘_OR’ with ‘OR’. The correct key for defining OR conditions in TypeORM is ‘OR’.

    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search