How to select fields from joined table using TypeORM repository?

In database operations with TypeORM, if you need to select specific fields from join tables (which contain foreign key relationships), you can use QueryBuilder to construct complex queries that include joins and selecting specific fields. Below, I'll provide a specific example demonstrating how to use QueryBuilder to achieve this from join tables.

Assume we have two entities: User and Photo, which are associated through a join table PhotoMetadata. The User entity contains basic user information, the Photo entity includes photo information, and PhotoMetadata contains metadata related to photos.

Entity Definitions

First, define these three entities:

typescript
@Entity()
export class User {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @OneToMany(() => Photo, photo => photo.user)
    photos: Photo[];
}

@Entity()
export class Photo {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    title: string;

    @ManyToOne(() => User, user => user.photos)
    user: User;

    @OneToOne(() => PhotoMetadata, photoMetadata => photoMetadata.photo)
    metadata: PhotoMetadata;
}

@Entity()
export class PhotoMetadata {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    description: string;

    @OneToOne(() => Photo, photo => photo.metadata)
    @JoinColumn()
    photo: Photo;
}

Using QueryBuilder for Queries

Next, if you want to select the description field from the PhotoMetadata table while also retrieving the title of the associated Photo and the name of its related User, you can use the following QueryBuilder:

typescript
import { getRepository } from "typeorm";

async function getPhotoInformation() {
    const photoRepository = getRepository(Photo);

    const photos = await photoRepository
        .createQueryBuilder("photo")
        .leftJoinAndSelect("photo.metadata", "metadata")
        .leftJoinAndSelect("photo.user", "user")
        .select([
            "photo.title",
            "metadata.description",
            "user.name"
        ])
        .getMany();

    return photos;
}

In this query:

• createQueryBuilder("photo") initializes a query targeting the Photo entity.
• leftJoinAndSelect("photo.metadata", "metadata") joins the PhotoMetadata entity to the query and selects it with the alias metadata.
• leftJoinAndSelect("photo.user", "user") joins the User entity to the query and selects it with the alias user.
• select([...]) specifies which fields to select from the query. Here, we select the photo's title, the metadata's description, and the user's name.
• getMany() retrieves the list of query results.

Using this query, you can efficiently select the required fields from multiple tables while maintaining query clarity and ease of management. This approach is particularly suitable for handling complex database relationships and large volumes of data.