How to translate an SQL statement to TypeORM query builder?

In TypeORM, you can use the Query Builder to construct and execute SQL queries. The Query Builder provides a chainable method for building queries, making it more flexible and secure than writing raw SQL statements directly. Below are the steps and examples for converting plain SQL statements to TypeORM's Query Builder code.

Assume we have a table named user, and we want to execute this SQL query:

sql
SELECT * FROM user WHERE age > 25 AND is_active = true;

To use the TypeORM Query Builder, follow these steps to convert the query:

1. Create a Query Builder instance.
2. Specify the entity or table to query.
3. Add filter conditions.
4. Execute the query.

Here is the corresponding TypeORM Query Builder code:

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

// Create a Repository instance
const userRepository = getRepository(User);

// Use Query Builder to construct the query
userRepository.createQueryBuilder('user')
    .where('user.age > :age', { age: 25 })
    .andWhere('user.isActive = :isActive', { isActive: true })
    .getMany().then(users => {
        // Process the results
        console.log(users);
    }).catch(error => {
        // Handle errors
        console.error(error);
    });

In the above code example, we first import the getRepository function and the User entity. We obtain the Repository for the User entity by calling getRepository. Then, we create a new Query Builder instance using createQueryBuilder and assign it the alias user. We add query conditions using where and andWhere. Finally, we execute the query using getMany and handle the results.

For more complex queries, such as joins, grouping, sorting, etc., the Query Builder also supports:

typescript
userRepository.createQueryBuilder('user')
    .leftJoinAndSelect('user.photos', 'photo')
    .where('user.age > :age', { age: 25 })
    .andWhere('user.isActive = :isActive', { isActive: true })
    .orderBy('user.name', 'ASC')
    .groupBy('user.id')
    .getMany();

In this example, we use leftJoinAndSelect for joins, orderBy for sorting, and groupBy for grouping.

This approach allows you to convert SQL statements to TypeORM's Query Builder code while maintaining readability and maintainability.