LIKE query with wildcard in TypeORM QueryBuilder

Question

In my NestJS project I have this TypeORM query:

const users = await this.usersRepository.find({
  skip,
  take,
  order: sortingObject,
  join: {
      alias: 'user',
      leftJoinAndSelect: {
          country: 'user.country_id',
      },
  },
});

Now I only want to return the users with John in there name. In SQL this would be a LIKE query LIKE %John%.

In https://github.com/typeorm/typeorm/blob/master/docs/find-options.md there is no information about wildcard LIKE queries.

How to perform a like query Typeorm gives as a solution: .where("user.firstName like :name", {name: '%' + firstName + '%' })

But then I am unable to use skip and take which is available when using where() instead of find().

Any thoughts on how I achieve this with the TypeORM QueryBuilder?

Answer 1

There are pagination methods (.skip(int) and .take(int)) in QueryBuilder.

Try something like this.

const users = await this.usersRepository
    .createQueryBuilder("user")
    .leftJoinAndSelect("user.country_id", "country")
    .skip(5)
    .take(10)
    .where("user.firstName like :name", {name: '%' + firstName + '%' })
    .orderBy("user.id", "DESC")
    .getMany();

For the details, please refer to the document: Using Pagination in TypeORM QueryBuilder