How to select data by using typeorm from 3 tables which depend on each other?-CodePudding

I have 3 entity which depend on each other and I have a problem with querying data from them by using one request.

First one User:

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

  @Column()
  firstName: string;

  @Column()
  lastName: string;

  @Column()
  login: string;

  @Column()
  password: string;

  @Column()
  ownerId: number;

  @OneToOne(() => Role, (role) => role.user)
  @JoinColumn()
  role?: Role;
}

Second one Role:

@Entity()
export class Role extends BaseEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column()
  description: string;

  @ManyToOne(() => User, (user) => user.role)
  @JoinColumn()
  user: User | null;

  @ManyToMany(() => Permission, { cascade: true })
  @JoinTable({ name: 'roles_has_permissions' })
  permissions: Permission[];
}

Third one Permission:

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

  @Column()
  name: PossiblePermissions;
}

How to select data from database. I’m using typeorm with Postgresql and I want to get array like this.

[{
 …user info by ownerId
 role: {
  …role which related this user
  permissions: [{
   …permissions which related this role
  }, …]
 }
}, …]

CodePudding user response：

Use query builder https://typeorm.io/#/select-query-builder

In your case it should be something like this:

await connection
    .getRepository(User)
    .createQueryBuilder("user")
    .leftJoinAndSelect("user.role", "role")
    .leftJoinAndSelect("role.permissions", "permissions")
    .getMany()