How to retrieve a nested relationship using TypeORM

Given a OneToMany relationship to an entity containing a ManyToOne relationship, how do we retrieve the nested relationship's entity when making a MySQL query using TypeORM?

Let's say we have chairs, which are assigned to users via chairs assignments, and users. This would be our main entity's model:

import { Column, Entity, JoinColumn, ManyToOne, OneToMany, PrimaryColumn } from 'typeorm';

import { ChairsInterface } from './chairs.entity';
import { ChairsAssignmentsSchema } from './chairs_assignments.schema';

const CHAIRS_TABLE = 'chairs';

@Entity(CHAIRS_TABLE)
export class ChairsSchema implements ChairsInterface {
  @PrimaryColumn({
    nullable: false,
    type: Number,
  })
  id_chair: number;

  @OneToMany((type) => ChairsAssignmentsSchema, (assignment) => assignment.chair)
  @JoinColumn({ referencedColumnName: 'id_chair' })
  assignments: ChairsAssignmentsSchema[];
}

This, our first relationship's entity's schema:

import { Column, Entity, JoinColumn, ManyToOne, PrimaryColumn } from 'typeorm';

import { ChairssSchema } from './chairs.schema';
import { ChairsAssignmentsInterface } from './chairs_assignments.entity';
import { UsersSchema } from './users.schema';

const CHAIRS_ASSIGNMENTS_TABLE = 'chairs_assignments';

@Entity(CHAIRS_ASSIGNMENTS_TABLE)
export class ChairssAssignmentsSchema implements ChairsAssignmentsInterface {
  @PrimaryColumn({
    nullable: false,
    type: Number,
  })
  id_assignment: number;

  @Column({
    nullable: false,
    type: Number,
  })
  id_user: number;

  @ManyToOne(() => ChairsSchema, (tool) => tool.affectations)
  @JoinColumn({ name: 'id_chair' })
  chair: ChairsSchema;

  @ManyToOne(() => ChairsAssignmentsSchema, (assignment) => assignment.user)
  @JoinColumn({ name: 'id_user' })
  user: UsersSchema;
}

And that, our nested relationship's entity's schema:

import { Column, Entity, JoinColumn, ManyToOne, PrimaryColumn } from 'typeorm';

import { UsersInterface } from './users.entity';
import { UsersServicesSchema } from './users_service.schema';
import { UsersTypesSchema } from './users_type.schema';

const USERS_TABLE = 'users';

@Entity(USERS_TABLE)
export class UsersSchema implements UsersInterface {
  @Column({
    nullable: false,
    type: String,
  })
  email_user: string;

  @PrimaryColumn({
    nullable: false,
    type: Number,
  })
  id_user: number;

  @Column({
    nullable: false,
    type: String,
  })
  name_user: string;
}

Then, we first need to get the assignments, like this

    return this.repository
      .createQueryBuilder('chair')
      .leftJoinAndMapMany(
        'chair.assignments',
        ChairsAssignmentsSchema,
        'chairAssignment',
        'chair.id_chair=chairAssignment.id_chair',
      )
      .getMany();

And then each assignment's user, like that

    return this.repository
      .createQueryBuilder('chair')
      .leftJoinAndMapMany(
        'chair.assignments',
        ChairsAssignmentsSchema,
        'chairAssignment',
        'chair.id_chair=chairAssignment.id_chair',
      )
      .leftJoinAndMapOne(
        'chairAssignment.user',
        UsersSchema,
        'user',
        'chairAssignment.id_user=user.id_user',
      )
      .getMany();

And there you go! We have all our chairs, with every single one of their assignments, and each assignment's user!