表结构

user 用户表结构

course 课程表结构

user_course 用户课程表 (每个用户可以有多个课程, 每个课程可以有多个用户, 该表用以建立多对多关系)

实体

user.entity.ts

@Entity('user', { schema: 'test' })
export class User {
  @PrimaryGeneratedColumn({ type: 'int', name: 'id' })
  id: number;

  @Column('varchar', { name: 'name', nullable: true, length: 255 })
  name: string | null;

  @ManyToMany(() => Course, (course) => course.users)
  @JoinTable()
  courses: Course[];
}

course.entity.ts

@Entity('course', { schema: 'test' })
export class Course {
  @PrimaryGeneratedColumn({ type: 'int', name: 'id' })
  id: number;

  @Column('varchar', {
    name: 'name',
    nullable: true,
    comment: '课程名称',
    length: 255,
  })
  name: string | null;

  @ManyToMany(() => User, (user) => user.courses)
  users: User[];
}

查询

    // QueryBuilder查询
    const result2 = await this.userRepository
      .createQueryBuilder('user')
      .leftJoinAndSelect('user.courses', 'course')
      .getMany();

sql打印

SELECT
	`User`.`id` AS `User_id`,
	`User`.`name` AS `User_name`,
	`User__User_courses`.`id` AS `User__User_courses_id`,
	`User__User_courses`.`name` AS `User__User_courses_name` 
FROM
	`user` `User`
	LEFT JOIN `user_courses_course` `User_User__User_courses` ON `User_User__User_courses`.`userId` = `User`.`id`
	LEFT JOIN `course` `User__User_courses` ON `User__User_courses`.`id` = `User_User__User_courses`.`courseId`

很明显, 我们中间表的名称错误 (非 user_courses_course)

关联字段名称错误 (非userId 和 courseId)

上一篇文章写了 @JoinColumn()装饰器可以指定关联字段的名称, 那么 @JoinTable() 自然也是可以的

设置中间表名称/关联字段名称

@JoinTable() 装饰器设置

  @JoinTable({
    name: 'user_course',
    joinColumn: { name: 'user_id' },
    inverseJoinColumn: { name: 'course_id' },
  })

sql预览

SELECT
	`user`.`id` AS `user_id`,
	`user`.`name` AS `user_name`,
	`course`.`id` AS `course_id`,
	`course`.`name` AS `course_name` 
FROM
	`user` `user`
	LEFT JOIN `user_course` `user_course` ON `user_course`.`user_id` = `user`.`id`
	LEFT JOIN `course` `course` ON `course`.`id` = `user_course`.`course_id`