How to subqueries in TypeORM?

In TypeORM, executing subqueries is a highly valuable feature that enables us to construct complex queries for efficiently retrieving data from the database. TypeORM offers multiple approaches to execute subqueries, including the use of QueryBuilder and Repository API. Below, I'll demonstrate how to execute subqueries using QueryBuilder with a specific example.

Assume we have an entity named User containing user information and an entity named Photo containing details about user photos. Each user can have multiple photos. Now, we want to query the latest photo for each user.

First, we need to establish entity relationships. We won't delve into the creation of entities or the mapping of relationships here; instead, we'll focus directly on building the query.

Using TypeORM's QueryBuilder, we can write the query as follows:

typescript
import { getRepository, createQueryBuilder, Brackets } from "typeorm";
import { User } from "./entity/User";
import { Photo } from "./entity/Photo";

async function findLatestPhotoForEveryUser() {
    const usersWithLatestPhoto = await getRepository(User)
        .createQueryBuilder("user")
        .leftJoinAndSelect(subQuery => {
            return subQuery
                    .select("photo.userId", "userId")
                    .addSelect("MAX(photo.id)", "maxPhotoId")
                    .from(Photo, "photo")
                    .groupBy("photo.userId");
        }, "latestPhoto", "latestPhoto.userId = user.id")
        .leftJoinAndSelect("photo", "photo", "photo.id = latestPhoto.maxPhotoId")
        .getMany();

    return usersWithLatestPhoto;
}

This query consists of two parts:

1. Creating the Subquery: We first create a subquery to identify the ID of the latest photo for each user. This is accomplished by grouping the Photo table and selecting the maximum id value.

2. Joining the Subquery with the Main Query: Then, we join the subquery results to the main query using leftJoinAndSelect. Here, we link the User and Photo tables to ensure each user is associated with their latest photo.

Finally, we retrieve a list of all users along with their latest photos using the getMany() method.

This example illustrates how to leverage TypeORM's robust capabilities to execute complex subqueries, effectively managing data within the database.