TypeORM如何不预先查询关联关系向带复合外键的表插入数据

问题原因 #

你遇到的外键字段为空是两个核心原因导致的：

1. EmployeePayroll实体只配置了关联关系的映射，没有显式声明外键对应的实体字段，TypeORM默认不会把你传入的employeeId、employeeVersion自动映射到employee_id、employee_version数据库列。
2. 用QueryBuilder执行插入时，如果传入的字段名没有对应到实体字段或者数据库列名，会被直接忽略。

方案1：显式声明外键字段（最推荐，后续使用最灵活） #

直接在EmployeePayroll实体中补充外键字段的声明，既不影响关联查询，也可以直接赋值插入：

import {
  Column,
  Entity,
  JoinColumn,
  ManyToOne,
  PrimaryGeneratedColumn,
} from 'typeorm';
import { Employee } from '../../employee/entities/employee.entity';

@Entity({ name: 'employee_payrolls' })
export class EmployeePayroll {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({ name: 'employee_payroll_name', nullable: true })
  employeePayrollName: string;

  // 新增显式外键字段
  @Column({ name: 'employee_id' })
  employeeId: string;

  @Column({ name: 'employee_version' })
  employeeVersion: number;

  @ManyToOne(() => Employee)
  @JoinColumn([
    { name: 'employee_id', referencedColumnName: 'id' },
    { name: 'employee_version', referencedColumnName: 'version' },
  ])
  employee: Employee;
}

修改后不需要改服务层代码，直接传入DTO即可正常插入外键值。

方案2：传入完整关联实体（适合插入前需要校验关联数据是否存在的场景） #

如果不想额外加外键字段，可以先查询出对应的Employee实例，再赋值给关联字段插入：

// 注意先注入Employee的Repository，或者用manager查询
@Injectable()
export class EmployeePayrollService {
  constructor(
    @InjectRepository(EmployeePayroll)
    private readonly employeePayrollRepository: Repository<EmployeePayroll>,
    @InjectRepository(Employee)
    private readonly employeeRepository: Repository<Employee>,
  ) {}

  async addEmployeePayroll(employeePayroll: EmployeePayrollDTO) {
    // 先校验员工是否存在
    const existEmployee = await this.employeeRepository.findOne({
      where: {
        id: employeePayroll.employeeId,
        version: employeePayroll.employeeVersion
      }
    })
    if(!existEmployee) throw new Error('对应员工不存在')

    return await this.employeePayrollRepository
      .createQueryBuilder()
      .insert()
      .into(EmployeePayroll)
      .values({
        employeePayrollName: employeePayroll.employeePayrollName,
        employee: existEmployee
      })
      .execute();
  }
}

注：原代码中Repository命名为employeeRepository容易混淆，建议修改为employeePayrollRepository避免后续出错

方案3：直接指定数据库列名插入（适合临时快速插入，不需要额外修改实体的场景） #

不需要修改实体，直接在插入时指定数据库的外键列名作为键即可：

async addEmployeePayroll(employeePayroll: EmployeePayrollDTO) {
  return await this.employeeRepository
    .createQueryBuilder()
    .insert()
    .into(EmployeePayroll)
    .values({
      employeePayrollName: employeePayroll.employeePayrollName,
      employee_id: employeePayroll.employeeId,
      employee_version: employeePayroll.employeeVersion
    })
    .execute();
}

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