How to add a where clause dynamically to query which is generated using nestjs Query Builder?

In NestJS, when using the Query Builder, you can dynamically add WHERE clauses by chaining the .where() or .andWhere() methods. This approach is highly beneficial for dynamically constructing queries based on varying conditions.

Here are the steps and examples for dynamically adding WHERE clauses in a NestJS project using TypeORM:

1. Obtain the Query Builder: First, obtain a Query Builder instance from your repository or entityManager.

typescript
const queryBuilder = repository.createQueryBuilder('entityAlias');

1. Basic Query: Set up a basic query that may include only the necessary SELECT and FROM parts.

typescript
queryBuilder.select('entityAlias').from(Entity, 'entityAlias');

1. Dynamically Add WHERE Clauses: Use conditional statements (such as if statements) to dynamically add WHERE clauses based on business logic. Apply .where() for the initial condition and .andWhere() for subsequent conditions.

typescript
if (condition1) {
  queryBuilder.where('entityAlias.fieldName = :value', { value: conditionValue1 });
}

if (condition2) {
  queryBuilder.andWhere('entityAlias.anotherFieldName = :anotherValue', { anotherValue: conditionValue2 });
}

1. Execute the Query: After building the query, use methods like .getMany() or .getOne() to execute the query and retrieve results.

typescript
const results = await queryBuilder.getMany();

Below is a specific example demonstrating how to dynamically add WHERE clauses based on user input to filter users.

typescript
import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { User } from './user.entity';

@Injectable()
export class UserService {
  constructor(
    @InjectRepository(User) private userRepository: Repository<User>,
  ) {}

  async findUsersWithDynamicConditions(username?: string, email?: string): Promise<User[]> {
    const queryBuilder = this.userRepository.createQueryBuilder('user');

    if (username) {
      queryBuilder.where('user.username = :username', { username });
    }

    if (email) {
      queryBuilder.andWhere('user.email = :email', { email });
    }

    return await queryBuilder.getMany();
  }
}

In this example, the findUsersWithDynamicConditions method accepts username and email as optional parameters and dynamically constructs the query based on them. If username is provided, it adds a WHERE clause for username; if email is provided, it adds a WHERE clause for email. This enables flexible query construction based on provided parameters rather than a fixed query.

Dynamically building queries is a common requirement in database operations. Mastering this skill enhances your code's flexibility and power.