Optional parameter in TypeORM queryBuilder

Question

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?

Answer 1

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();

Answer 2

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.