Optional parameter in TypeORM queryBuilder

Admin 1
Viewed 6916

Is it possible to use optional parameters in createQueryBuilder?

for example, I have this code:

let users = await this.conn
  .getRepository(UserEntity)
  .createQueryBuilder("user")
  .where("user.firstName LIKE :search", { search: dto.search })
  .getMany();

my optional parameter is search, I want to launch clauses .where only when dto.search is not null, but when he is null then should skip this function(where) and goes to getMany.

Have anybody any idea how to do this?

typeorm nestjs typescript sql javascript
2 Answers
const query = this.conn
  .getRepository(UserEntity)
  .createQueryBuilder("user");

// Optionally add where condition
if(dto.search){
  query.where("user.firstName LIKE :search", { search: dto.search })
}
// run query
let users = await query.getMany();

Do be mindful of falsy values that would trip up the if statement.

user_19203260

Try this:

let users = await this.conn.getRepository(UserEntity)
  .createQueryBuilder('user')
  .where(search !== null 
    ? 'user.firstName LIKE :search'
    : 'TRUE', { search: dto.search })
  .getMany();

Or another way to do without the where TRUE is:

let users = this.conn.getRepository(UserEntity)
  .createQueryBuilder('user');
users = search !== null 
  ? users.where('user.firstName LIKE :search',{ search: dto.search })
  : users
users = await users.getMany();
user_134245330
Related