CODEWITHSUNDEEP
mysqlnode.jsmany-to-manynestjstypeorm
kimHD12
kimHD12

Reputation: 13

TypeOrm LeftJoin with 3 tables

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

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 :(

Upvotes: 1

Views: 22230

Answers (1)

Elenka29
Elenka29

Reputation: 147

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

Upvotes: 4

Related Questions