CODEWITHSUNDEEP
nestjstypeormtypeorm-datamapper
Bas van Dijk
Bas van Dijk

Reputation: 10713

LIKE query with wildcard in TypeORM QueryBuilder

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?

Upvotes: 3

Views: 22965

Answers (4)

MrHIDEn
MrHIDEn

Reputation: 1879

With MongoDB and .find() I use own function MLike - MongoDB-Like.
TypeScript

const user = await this.usersRepository.find({
  where: MLike(`%${name}`),
});

Where function body is

export function MLike(value: string): { $regex: string } {
    const like = value.replace(/%+/g, '.*');
    return {$regex: `^${like}$`};
}
// example of use, MLike(`%1234`); -> { '$regex': '^.*1234$' }

Upvotes: 0

edalguerr
edalguerr

Reputation: 11

To complement the above answer written by Hungnn

To search with find().
You can use ILike from 'typeorm' to search with LIKE query.

In your case:

  const users = await this.usersRepository.find({
  where: {
    id: criteria?.id,
    name: ILike(`%${criteria?.name || ''}%`),
  },
  relations: {
    country: true
  },
});

You also can use @ManyToMany() or @ManyToOne() with @JoinTable({ name: 'task_category' }) in your Entity class to avoid:

join: {
  alias: 'user',
  leftJoinAndSelect: {
      country: 'user.country_id',
  },

Upvotes: 1

Luke
Luke

Reputation: 175

To search with find().
You can use ILike from 'typeorm' to search with LIKE query.

In your case:

const users = await this.usersRepository.find({
  skip,
  take,
  where: {
      name: ILike(`%${ firstName || ''}%`)
  },
  order: sortingObject,
  join: {
      alias: 'user',
      leftJoinAndSelect: {
          country: 'user.country_id',
      },
  },
});

Note that, import { ILike } from 'typeorm';

Upvotes: 0

Michael Lam
Michael Lam

Reputation: 445

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

Upvotes: 8

Related Questions