TypeORM, add condition in `where` if value is presented and not empty string

user_842225 0
Viewed 2162

I am using TypeOrm in my node.js project. I know to find a record from database I can do :

userRepository.find({ where: { firstName: "John" } });

It executes query:

SELECT * FROM "user"
WHERE "firstName" = 'John'

But now I need do add another filed check in "where" condition only if the value is presented. For example, I want to also check company in SQL "where" condition, but only if company value is presented.

I tried following, I wonder can I do the following by giving a default empty string '' if company doesn't present then pass it to find function's where?

const company = params.company ? params.company : '';

userRepository.find({ where: { firstName: "John", company: company } });

But it would still add "company"='' in the final SQL query which is not good. I wonder is there an existing function in TypeORM that could dynamically decide only add more condition in where if value is presented and not empty string?

node.js-typeorm typeorm nestjs node.js
3 Answers

You can use destruction for it. Like:

userRepository.find({
   where: {
      firstName: "John",
      ...(params?.company && { company: params.company }),
   }
});

so, if params.company is undefined (or empty string) then

...(undefined && { company: undefined })

returns undefined and for the destruction it like ...{} for you.

if params.company contain some value, the

...('company' && { company: 'company' })

returns ...{company: 'company'} and destruct this for your where.

Example:

const companyTestWithValue = 'company';
const companyTestWithoutValue = '';

const whereWithValue = {
  firstName: 'John',
  ...(companyTestWithValue && { company: companyTestWithValue }),
};

const whereWithoutValue = {
  firstName: 'John',
  ...(companyTestWithoutValue && { company: companyTestWithoutValue }),
};

console.log('whereWithValue:', whereWithValue);
console.log('whereWithoutValue:', whereWithoutValue);

user_58603090

You can create a find condition dynamically, example:

const condition = { where: { firstName: "John" } };

if (params.company) {
    condition.where = {
        ...condition.where,
        company: params.company
    }
}

const user = userRepository.find(condition);
user_86605270

I don't think there will be a pretty solution to this. Your best bet will probably to just use an if - else statement. TypeOrm will create a statement using whatever was passed to it. I suggest just doing:

      if(params.company){
        userRepository.find({ where: { firstName: "John", company: company } });
      } else{
        userRepository.find({ where: { firstName: "John" } });
      }
user_27522590
Related