I create QueryBuilder to fetch my relation data. So in my case, I need to validate some of my get req params before the index. So in this case I created a common QueryBuilder instance like this.

// Common Get Query
      const result = await this.reservationRepo
        .createQueryBuilder()
        .leftJoinAndSelect('Reservation.userId', 'userId')
        .leftJoinAndSelect('Reservation.companyId', 'companyId')
        .leftJoinAndSelect('Reservation.petId', 'petId')
        .orderBy(`Reservation.${sortBy}`, order)
        .where('Reservation.isArchived = :isArchived', { isArchived: false });

and then I wrote my validation logic like this;

//Check req starts is cancelled and return canceled data (getting data from db)
  if (getPaginationParamDto.status === GetReservationTypes.CANCELLED) {
    if (type === GetReservationTypes.COMPANY) {
      result.where('Reservation.companyId = :companyId', { companyId: `${searchID}` });
    } else {
      result.where('Reservation.userId = :userId', { userId: `${searchID}` });
    }
    result.where('Reservation.status = :status', { status: getPaginationParamDto.status });

But the problem is my where

isArchived: false

is not working.

.where('Reservation.isArchived = :isArchived', { isArchived: false });

So in this case, I wrote my isArchived where Query after my logics. (end of everything) after that, it's working. But my condition is not applied for that. So I really need your help with this case. Thanks.

CodePudding user response：

According to TypeORM's documentation on adding WHERE expressions:

[...] If you use .where more than once you'll override all previous WHERE expressions.

So you need to revise your logic to use .andWhere() instead if you want to apply multiple WHERE clauses to your query.

Maybe something like:

const result = await this.reservationRepo
  .createQueryBuilder()
  .leftJoinAndSelect("Reservation.userId", "userId")
  .leftJoinAndSelect("Reservation.companyId", "companyId")
  .leftJoinAndSelect("Reservation.petId", "petId")
  .orderBy(`Reservation.${sortBy}`, order)
  .where("Reservation.isArchived = :isArchived", { isArchived: false });

// [...]

if (getPaginationParamDto.status === GetReservationTypes.CANCELLED) {

  if (type === GetReservationTypes.COMPANY) {
    result.andWhere("Reservation.companyId = :companyId", { companyId: `${searchID}` });
  } else {
    result.andWhere("Reservation.userId = :userId", { userId: `${searchID}` });
  }

  result.andWhere("Reservation.status = :status", { status: getPaginationParamDto.status });

// [...]

However, you might want to remove the await on line 1 if you're trying to add these additional WHERE clauses BEFORE executing the query. Finish building the query before executing it.