Typeorm Query Builder filtering multiple columns with one value

user_11235678 0
Viewed 1259

My aim is to hit api endpoint and only pass one query variable and try to filter out any of the columns based on single variable.

I have multiple users in database like:

{
  id: 1,
  email: 'test+1@test.com',
  username: 'test',
  phone_number: '+651231231',
  skillsets: 'Drawing, cooking',
  hobby: 'Sleeping',
},
{
  id: 2,
  email: 'test+2@test.com',
  username: 'test 2',
  phone_number: '+6512312311231',
  skillsets: 'Drawing, cooking',
  hobby: 'Drawing',
}

My function/Query

    let users = this.repo.createQueryBuilder('user');

    if (query && query !== '') {
      users = users
        .where('user.email like :email', { email: `%${query}%` })
        .orWhere('user.username like :username', {
          username: `%${query}%`,
        })
        .orWhere('user.phone_number like :phone_number', {
          phone_number: `%${query}%`,
        })
        .orWhere('user.skillsets like :skillsets', {
          phone_number: `%${query}%`,
        })
        .orWhere('user.hobby like :hobby', { hobby: `%${query}%` });
    }

    return users.getMany();

My api endpoint would be something like

/api/users?query=Sleeping

I can't filter by anything else other than email and I assumed is because of initial where but I am not entirely sure how to cater for this scenario.

I appreciate if anyone can point me to the right direction.

typeorm nestjs typescript node.js
1 Answers

I was looking to implement something similar and I found the answer in another Stackoverflow Question

It is possible to use OR clause using the simple typeorm .find() options as described in TypeORM docs. To query with OR operator you'll need to use an array of conditions instead of an object.

let users = await user.find({
    where: [
        { email: Like(%${query}%) },
        { skillsets: Like(%${query}%) },
        { username: Like(%${query}%) }
    ]
});
user_94155110
Related