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

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?

CodePudding user response：

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

CodePudding user response：

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" } });
      }

CodePudding user response：

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