NestJS中TypeORM三表左连接实现及关联配置问题排查：多对多关联生成冗余表

Let me break down what's going wrong here and walk you through the correct setup step by step:

Why Your Current Setup Is Failing #

1. Wrong Association Type: Your Role table isn't a plain many-to-many join table—it has an extra level field. Using @ManyToMany tells TypeORM to auto-generate additional join tables (which explains the 4 tables you're seeing), but your Role entity should act as the join table itself with custom data.
2. Typo in UserEntity: You accidentally wrote @ManyToMany(() => UserEntity, role => role.users) instead of targeting RoleEntity—that's a clear syntax mistake that breaks the association.

Correct Entity Association Configuration #

We need to use @OneToMany + @ManyToOne relationships here, since a single user/establishment can have multiple role entries, and each role entry belongs to exactly one user and one establishment.

UserEntity #

import { Entity, PrimaryGeneratedColumn, Column, OneToMany } from 'typeorm';
import { RoleEntity } from './role.entity';

@Entity('user')
export class UserEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  // One user can have multiple role entries (across different establishments)
  @OneToMany(() => RoleEntity, role => role.user)
  roles: RoleEntity[];
}

EstablishmentEntity #

import { Entity, PrimaryGeneratedColumn, Column, OneToMany } from 'typeorm';
import { RoleEntity } from './role.entity';

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

  @Column()
  name: string;

  // One establishment can have multiple role entries (for different users)
  @OneToMany(() => RoleEntity, role => role.establishment)
  roles: RoleEntity[];
}

RoleEntity #

We'll set user_id and establishment_id as a composite primary key (since a user can only have one role per establishment) and add the level field:

import { Entity, PrimaryColumn, Column, ManyToOne, JoinColumn } from 'typeorm';
import { UserEntity } from './user.entity';
import { EstablishmentEntity } from './establishment.entity';

@Entity('role')
export class RoleEntity {
  // Composite primary key: unique combination of user + establishment
  @PrimaryColumn()
  user_id: number;

  @PrimaryColumn()
  establishment_id: number;

  @Column()
  level: string;

  @ManyToOne(() => UserEntity, user => user.roles)
  @JoinColumn({ name: 'user_id' })
  user: UserEntity;

  @ManyToOne(() => EstablishmentEntity, establishment => establishment.roles)
  @JoinColumn({ name: 'establishment_id' })
  establishment: EstablishmentEntity;
}

Implementing Left Joins #

Now you can perform left joins using TypeORM's QueryBuilder to fetch related data. Here are a few common examples:

Example 1: Get all users with their roles and associated establishments #

async getUsersWithRoles(): Promise<UserEntity[]> {
  return this.userRepository.createQueryBuilder('user')
    .leftJoinAndSelect('user.roles', 'role')
    .leftJoinAndSelect('role.establishment', 'establishment')
    .getMany();
}

Example 2: Get a specific establishment with all its users and their roles #

async getEstablishmentWithUsers(estId: number): Promise<EstablishmentEntity> {
  return this.establishmentRepository.createQueryBuilder('establishment')
    .leftJoinAndSelect('establishment.roles', 'role')
    .leftJoinAndSelect('role.user', 'user')
    .where('establishment.id = :id', { id: estId })
    .getOne();
}

Example 3: Get all roles with their linked user and establishment #

async getAllRolesWithRelations(): Promise<RoleEntity[]> {
  return this.roleRepository.createQueryBuilder('role')
    .leftJoinAndSelect('role.user', 'user')
    .leftJoinAndSelect('role.establishment', 'establishment')
    .getMany();
}

Inserting Role Data Correctly #

With the fixed associations, inserting role records works as expected—just link existing user and establishment entities:

async createUserRole(userId: number, estId: number, level: string): Promise<RoleEntity> {
  // Fetch existing user and establishment (you could add error handling here)
  const user = await this.userRepository.findOneBy({ id: userId });
  const establishment = await this.establishmentRepository.findOneBy({ id: estId });

  const role = this.roleRepository.create({
    user_id: userId,
    establishment_id: estId,
    level,
    user,
    establishment
  });

  return this.roleRepository.save(role);
}

This setup will generate exactly the 3 tables you need, fix the insertion failures, and let you perform left joins to retrieve related data seamlessly.

内容的提问来源于stack exchange，提问作者kimHD12