How to combine QueryBuilder in TypeORM?

In TypeORM, QueryBuilder is a powerful tool that enables developers to construct SQL queries in a chainable manner, resulting in more flexible and readable queries. By combining QueryBuilder, we can build more complex and dynamic queries. Here are several steps and examples for combining QueryBuilder in TypeORM:

1. Basic QueryBuilder Usage

First, import TypeORM's getRepository method to begin constructing your query. For instance, to query a user table:

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

const userRepository = getRepository(User);

Then, use QueryBuilder to build a basic query:

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

2. Combining Multiple Conditions

We can add multiple conditions within a single QueryBuilder. For example, add sorting and limit the number of returned results:

typescript
const users = await userRepository.createQueryBuilder('user')
    .where('user.age > :age', { age: 18 })
    .orderBy('user.name', 'ASC')
    .take(10)
    .getMany();

3. Using Subqueries

In some cases, subqueries are necessary to further combine QueryBuilder. For example, query all users while retrieving the latest order information for each user:

typescript
const users = await userRepository.createQueryBuilder('user')
    .leftJoinAndSelect(subQuery => {
        return subQuery
            .select('order.userId', 'userId')
            .addSelect('MAX(order.createdAt)', 'latestOrderDate')
            .from(Order, 'order')
            .groupBy('order.userId');
    }, 'latestOrder', 'latestOrder.userId = user.id')
    .getMany();

4. Dynamically Combining Query Conditions

Sometimes, we need to dynamically adjust query conditions based on input parameters. This is achieved by progressively adding conditions to the QueryBuilder:

typescript
let query = userRepository.createQueryBuilder('user');

if (age) {
    query = query.where('user.age > :age', { age });
}

if (name) {
    query = query.andWhere('user.name LIKE :name', { name: `%${name}%` });
}

const users = await query.getMany();

5. Complex Multi-Table Queries

For queries involving multiple tables, combine QueryBuilder using multiple leftJoinAndSelect:

typescript
const users = await userRepository.createQueryBuilder('user')
    .leftJoinAndSelect('user.profile', 'profile')
    .leftJoinAndSelect('user.orders', 'orders')
    .where('user.isActive = :isActive', { isActive: true })
    .andWhere('orders.status = :status', { status: 'shipped' })
    .getMany();

This approach not only makes the code clearer and more maintainable but also allows flexible handling of various complex database query requirements.