How to select only single/multiple fields from joined entity in Typeorm

In TypeORM, if you want to select single or multiple fields from joined entities, you can use the QueryBuilder to build a more flexible SQL query. This approach enables you to precisely control data selection and transmission during the query process. I will demonstrate how to achieve this with a specific example.

Assume we have two entities: User and Profile, which have a one-to-one relationship. Our goal is to query the username of each user along with their corresponding email, which is stored in the Profile entity.

First, we need to ensure that the relationships between our entities are correctly set up. For example, the User entity might look like this:

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

    @Column()
    username: string;

    @OneToOne(() => Profile, profile => profile.user)
    profile: Profile;
}

The Profile entity might look like this:

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

    @Column()
    email: string;

    @OneToOne(() => User, user => user.profile)
    @JoinColumn()
    user: User;
}

Now, to select only the email field from the Profile entity, we can use TypeORM's QueryBuilder to construct the following query:

typescript
import { getRepository } from "typeorm";

async function findUserWithEmail() {
    const userRepository = getRepository(User);
    const users = await userRepository.createQueryBuilder("user")
        .leftJoinAndSelect("user.profile", "profile")
        .select(["user.username", "profile.email"])
        .getMany();

    return users;
}

In this query:

• "user" is the alias for the User entity.
• leftJoinAndSelect("user.profile", "profile") performs a left outer join with the Profile entity and assigns the alias profile.
• select(["user.username", "profile.email"]) specifies that only the username and email fields should be retrieved.
• getMany() executes the query and returns multiple results.

This approach improves data loading efficiency by selecting only necessary fields, reducing data transmission volume. Additionally, using the QueryBuilder allows flexible query adjustments to accommodate more complex business requirements.