How to use sub queries in queryBuilder in TypeORM

Using subqueries in TypeORM's QueryBuilder enhances query flexibility and power, allowing you to build complex queries, especially when referencing data from multiple tables within a single query. Below are the basic methods for using subqueries in TypeORM, along with relevant examples.

Basic Methods

In TypeORM's QueryBuilder, you can use the subQuery method to create subqueries. You can embed subqueries in clauses such as SELECT, FROM, or WHERE, depending on your needs.

Examples

Assume we have two entities: User and Photo, where the User entity has multiple Photo entities. Now, we want to find the latest photo for each user.

1. Create a basic subquery

We first create a subquery using subQuery that returns the latest photo date for each user:

typescript
const latestPhotoDateSubQuery = qb.subQuery()
    .select("MAX(photo.createdAt)", "latestDate")
    .from(Photo, "photo")
    .where("photo.userId = user.id")
    .getQuery();

1. Use the subquery in the main query

Then, we can use this subquery in the main query to retrieve the latest photo for each user:

typescript
const usersWithLatestPhoto = await connection.getRepository(User)
    .createQueryBuilder("user")
    .leftJoinAndSelect((subQuery) => {
        return subQuery
            .select("photo")
            .from(Photo, "photo")
            .where("photo.createdAt = (" + latestPhotoDateSubQuery + ")");
    }, "latestPhoto", "latestPhoto.userId = user.id")
    .getMany();

In this example, we first define a subquery latestPhotoDateSubQuery that retrieves the latest photo date for each user. Then, in the main query, we use the leftJoinAndSelect method with a callback function to embed the subquery. This callback returns a subquery for the Photo entity, and the WHERE clause incorporates the previously defined subquery. This allows us to query each user along with their latest photo.

Summary

TypeORM's QueryBuilder provides powerful tools for constructing complex SQL queries, where subqueries enable multi-layered and conditionally complex data queries. By appropriately using subqueries, you can effectively resolve data relationships and filtering at the database level, thereby enhancing application performance and data processing capabilities.