TypeOrm LeftJoin with 3 tables

Question

i want to create Left Join between 3 tables in TypeORM (NesjJS)

• Table 1: User (can have multiple establishments with multiple roles)
• Table 2: Establishment (can be associated to multiple users having multiple roles)
• Table 3: Role (listing associations between User/Establishment and role Level)

Example:

==> "user" table

ID	name
1	jiji
2	jaja
3	jojo
4	jeje

==> "establishment" table

ID	name
10	est 1
11	est 2
12	est 3

==> "role" table

user_id	establishment_id	level
1	10	admin
1	11	editor
2	10	editor
3	11	reader
3	12	admin

i try with this relations but do not working form me :( when i try to insert data to role entity

export class EstablishmentEntity{
  @PrimaryGeneratedColumn()
  id: number;

  ...
  @ManyToMany(() => RoleEntity, role => role.establishments)
  roles: Role[];
}

export class UserEntity{
  @PrimaryGeneratedColumn()
  id: number;
  ...

  @ManyToMany(() => UserEntity, role => role.users)
  roles: Role[];
}

export class RoleEntity{
  @PrimaryGeneratedColumn()
  id: number;

  ...
  @ManyToMany(() => UserEntity, user => user.roles)
  users: UserEntity[];


  @ManyToMany(() => EstablishmentEntity, establishment => establishment.roles)
  establishments: EstablishmentEntity[];
}

and JoinTable create for me 4 tables :(

Answer 1

From what I've understood - you would like to fetch users with all their establishments and level inside. Consider you have custom column level there you need to use OneToMany reference in UserEntity to roles and EstablishmentEntity and ManyToOne in RoleEntity instead of ManyToMany. ManyToMany would be useful if you would only have User and Establishments ids in 3rd table and wanted to fetch all Establishments by user at once.

// RoleEntity:

...
@ManyToOne(() => UserEntity, user => user.roles)
public user: UserEntity;

@ManyToOne(() => EstablishmentEntity, establishment => establishment.roles)
public establishment: EstablishmentEntity;

// UserEntity:

...
@OneToMany(() => RoleEntity, roles => roles.user)
public roles: RoleEntity[];

// EstablishmentEntity

...
@OneToMany(() => RoleEntity, roles => roles.establishment)
public roles: RoleEntity[];

After you will be able to fetch data using left joins:

const usersWithRoles = await connection
.getRepository(User)
.createQueryBuilder("user")
.leftJoinAndSelect("user.roles", "roles")
.leftJoinAndSelect("roles.establishment", "establishment")
.getMany();

It will return for you all users with their roles and in certain establishments. If you would like to save new role, you can do it like this:

await connection
.getRepository(RoleEntity)
.createQueryBuilder()
.insert()
.values({user, establishment, level })
.execute()

Please refer to this part of documentation