How to make complex nested where conditions with typeORM?

When using TypeORM for database queries, in many scenarios, we need to construct complex nested WHERE conditions to meet business requirements. TypeORM provides several methods to achieve this, primarily by using the QueryBuilder to construct flexible SQL queries. Below are some examples and steps illustrating how to generate complex nested WHERE conditions.

1. Basic Usage of QueryBuilder

QueryBuilder is a powerful tool in TypeORM for constructing complex SQL queries. You can start building queries using getRepository or createQueryBuilder methods.

typescript
import { getRepository } from "typeorm";
import { User } from "./entity/User";

const userRepository = getRepository(User);

const users = await userRepository.createQueryBuilder("user")
    .where("user.age > :age", { age: 18 })
    .andWhere("user.status = :status", { status: 'active' })
    .getMany();

2. Building Nested Conditions

For nested conditions, we can use the Brackets object to encapsulate nested query logic. Brackets can contain one or more conditions and can be nested, making it ideal for constructing complex query conditions.

typescript
import { Brackets } from "typeorm";

const users = await userRepository.createQueryBuilder("user")
    .where(new Brackets(qb => {
        qb.where("user.name = :name", { name: "John" })
          .orWhere("user.name = :name", { name: "Jane" });
    }))
    .andWhere(new Brackets(qb => {
        qb.where("user.age > :age", { age: 20 })
          .andWhere("user.status = :status", { status: 'active' });
    }))
    .getMany();

3. Dynamically Building Query Conditions

In some scenarios, we may need to dynamically add query conditions based on different business logic. QueryBuilder supports adding conditions dynamically during construction.

typescript
const queryBuilder = userRepository.createQueryBuilder("user");

if (needNameFilter) {
    queryBuilder.andWhere("user.name = :name", { name: "John" });
}

if (needAgeFilter) {
    queryBuilder.andWhere("user.age > :age", { age: 20 });
}

const users = await queryBuilder.getMany();

Summary

By using TypeORM's QueryBuilder and Brackets, we can flexibly construct queries with multi-layered nesting and conditional logic. This approach not only makes SQL queries more flexible but also makes the code clearer and easier to maintain.