TypeORM, need to add "WHERE IN (...)" in query condition & only when there is a value for it

Question

I am using TypeORM (version 0.2.40) in my node.js project written in TypeScript . I know to find a record from database I can do :

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

It executes query:

SELECT * FROM "user"
WHERE "firstName" = 'John' AND "company" = 'foo'

Now I have an array in typescript which might hold or might not hold integer values:

 const userIds = params.user_ids; // e.g. undefined or [1,2,3]

I would like my query to add WHERE userIds IN (1,2,3) if userIds contains such values otherwise don't add this where condition.

I tried (In this case params might contain a company or might not contain too):

const {params} = parseRequest();

query: SelectQueryBuilder<MyEntity> = ...;

query.where({
  firstName: 'John',
  ...(params.company && { company: params.company }), // add 'company' condition only if value exists
}).andWhere({/* how to do here have 'WHERE IN (1,2,3)' if value exist?*/});

I have two questions:

1. How to only add the "WHERE IN (...)" only if params.user_ids exist (same as for params.company)? (Please feel free to remove my andWhere usage if there is a neat way)

2. What is the syntax of TypeORM to add the "WHERE IN (...)" logic for params.user_ids assuming value exists?

Answer 1

You can use a simple conditional along with the IN operator. E.g.

import {In} from "typeorm";

let query = ...
if (userIds?.length) {
  // WHERE IN [...userIds]
  query = query.where({userId: In([...userIds])})
}
// Continue chaining.
query = ...

Resources:

• Conditional
• In
• Docs