How to make complex nested where conditions with typeORM?

Question

I am having multiple nested where conditions and want to generate them without too much code duplication with typeORM.

The SQL where condition should be something like this:

 WHERE "Table"."id" = $1
AND
"Table"."notAvailable" IS NULL
AND
(
  "Table"."date" > $2
  OR
  (
    "Table"."date" = $2
    AND
    "Table"."myId" > $3
  )
)
AND
(
  "Table"."created" = $2
  OR
  "Table"."updated" = $4
)
AND
(
  "Table"."text" ilike '%search%'
  OR
  "Table"."name" ilike '%search%'
)

But with the FindConditions it seems not to be possible to make them nested and so I have to use all possible combinations of AND in an FindConditions array. And it isn't possible to split it to .where() and .andWhere() cause andWhere can't use an Object Literal.

Is there another possibility to achieve this query with typeORM without using Raw SQL?

You might want to improve your question with code examples that didn't work or did not give the result you expected. Furthermore is always advisable to share the relevant entity (partially) and have the current code snippet you wanted to call this query from — Funonly
– Funonly, Commented May 11, 2021 at 10:53

Sufiane · Accepted Answer · 2021-09-13 16:10:35Z

42

When using the queryBuilder I would recommend using Brackets as stated in the Typeorm doc: https://typeorm.io/#/select-query-builder/adding-where-expression

You could do something like:

createQueryBuilder("user")
    .where("user.registered = :registered", { registered: true })
    .andWhere(new Brackets(qb => {
        qb.where("user.firstName = :firstName", { firstName: "Timber" })
          .orWhere("user.lastName = :lastName", { lastName: "Saw" })
    }))

that will result with:

SELECT ... 
FROM users user
WHERE user.registered = true 
AND (user.firstName = 'Timber' OR user.lastName = 'Saw')

answered Sep 13, 2021 at 16:10

Sufiane

2,3162 gold badges18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alisson Honostorio Over a year ago

Using the queryBuilder as you sujested, how to deal with it when the properties are optional, for example, if the lastName is a optional property, the ORM will return with exception? Or he will not find the entity because the property value is undefined?

Sufiane Over a year ago

I think then there will be no result based on the lastName as there are no rows matching Saw since there are all not defined in your DB. Therefore no matching possible.

Funonly · Accepted Answer · 2021-05-11 10:54:55Z

I think you are mixing 2 ways of retrieving entities from TypeORM, find from the repository and the query builder. The FindConditions are used in the find function. The andWhere function is use by the query builder. When building more complex queries it is generally better/easier to use the query builder.

Query builder

When using the query build you got much more freedom to make sure the query is what you need it to be. With the where you are free to add any SQL as you please:

const desiredEntity = await connection
  .getRepository(User)
  .createQueryBuilder("user")
  .where("user.id = :id", { id: 1 })
  .andWhere("user.date > :date OR (user.date = :date AND user.myId = :myId)",
    { 
      date: specificCreatedAtDate,
      myId: mysteryId,
    })
  .getOne();

Note that depending on your used database the actual SQL that you use here needs to be compatible. With that could also come a possible draw back of using this method. You will tie your project to a specific database. Make sure to read up about the aliases for tables you can set if you are using relations this would be handy.

Repository

You already saw that this is much less comfortable. This is because the find function or more specific the findOptions are using objects to build the where clause. This makes is harder to implement a proper interface to implement nested AND and OR clauses side by side. There for (I assume) they have chosen to split AND and OR clauses. This makes the interface much more declarative and means the you have to pull your OR clauses to the top:

const desiredEntity = await repository.find({
  where: [{
    id: id,
    notAvailable: Not(IsNull()),
    date: MoreThan(date)
  },{
    id: id,
    notAvailable: Not(IsNull()),
    date: date
    myId: myId
  }]
})

I cannot imagin looking a the size of the desired query that this code would be very performant.

Alternatively you could use the Raw find helper. This would require you to rewrite your clause per field, since you will only get access to the one alias at a time. You could guess the column names or aliases but this would be very poor practice and very unstable since you cannot directly control this easily.

Royer Adames · Accepted Answer · 2022-02-15 17:49:30Z

if you want to nest andWhere statements if a condition is meet here is an example:

async getTasks(filterDto: GetTasksFilterDto, user: User): Promise<Task[]> {
const { status, search } = filterDto;

/* create a query using the query builder */
// task is what refer to the Task entity
const query = this.createQueryBuilder('task');

// only get the tasks that belong to the user
query.where('task.userId = :userId', { userId: user.id });

/* if status is defined then add a where clause to the query */
if (status) {
  // :<variable-name> is a placeholder for the second object key value pair
  query.andWhere('task.status = :status', { status });
}
/* if search is defined then add a where clause to the query */
if (search) {
  query.andWhere(
    /* 
    LIKE: find a similar match (doesn't have to be exact)
      - https://www.w3schools.com/sql/sql_like.asp
    Lower is a sql method
    - https://www.w3schools.com/sql/func_sqlserver_lower.asp

    * bug: search by pass where userId; fix: () whole addWhere statement
    because andWhere stiches the where class together, add () to make andWhere  with or and like into a single where statement
     */
    '(LOWER(task.title) LIKE LOWER(:search) OR LOWER(task.description) LIKE LOWER(:search))',
    // :search is like a param variable, and the search object is the key value pair. Both have to match
    { search: `%${search}%` },
  );
}
/* execute the query

- getMany means that you are expecting an array of results
 */
let tasks;
try {
  tasks = await query.getMany();
} catch (error) {
  this.logger.error(
    `Failed to get tasks for user "${
      user.username
    }", Filters: ${JSON.stringify(filterDto)}`,
    error.stack,
  );
  throw new InternalServerErrorException();
}
return tasks;

}

vanduc1102 · Accepted Answer · 2022-04-01 09:14:04Z

I have a list of

{ 
   date: specificCreatedAtDate,
   userId: mysteryId
}

My solution is

.andWhere(
      new Brackets((qb) => {
        qb.where(
          'userTable.date = :date0 AND userTable.type = :userId0',
          {
            date0: dates[0].date,
            userId0: dates[0].type,
          }
        );

        for (let i = 1; i < dates.length; i++) {
          qb.orWhere(
            `userTable.date = :date${i} AND userTable.userId = :userId${i}`,
            {
              [`date${i}`]: dates[i].date,
              [`userId${i}`]: dates[i].userId,
            }
          );
        }
      })
    )

That will produce something similar

const userEntity = await repository.find({
  where: [{
    userId: id0,
    date: date0
  },{
    id: id1,
    userId: date1
  }
  ....
]
})

Developer Sabbir · Accepted Answer · 2023-04-12 17:19:58Z

0

yeah it's true if we use querybuilder() then we can use too many options.

Don't worry, we can do it like querybuilder() In the down below have an example to use NOT, and also LIKE for search/query.

const users = await User.find({
        where: {
          username: Like(`%${req.body?.search}%`),
          uuid: Not(req.user?.uuid),
        },
        select: {
          id: true,
          uuid: true,
          username: true,
          name: true,
          status: true,
          image: true,
        },
      });

After that, we can send response as json data.

res.status(200).json({message: "The query user got it.", users});

One thing for this purpose I'm using typescript
Remember it. Thanks

answered Apr 12, 2023 at 17:19

Developer Sabbir

3843 silver badges11 bronze badges

Collectives™ on Stack Overflow

How to make complex nested where conditions with typeORM?

5 Answers 5

2 Comments

Query builder

Repository

Comments

Comments

Comments

yeah it's true if we use querybuilder() then we can use too many options.

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

yeah it's true if we use querybuilder() then we can use too many options.

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related