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?

Answer 1

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