如何在NestJS+PostgreSQL中用TypeORM将两表ID合并到同一列

你遇到的问题是因为直接给两个@ManyToOne关联指定同一列时，TypeORM会将它们视为两个独立字段，进而生成两列。要实现approval表的document_id同时关联vendor和purchase的id，需要用到多态关联方案，具体实现步骤如下：

1. 定义枚举区分关联类型 #

先创建一个枚举，用来标记document_id关联的是Vendor还是Purchase：

export enum DocumentType {
  VENDOR = 'vendor',
  PURCHASE = 'purchase',
}

2. 修改Approval实体 #

在Approval实体中添加documentId（存储关联ID）和documentType（标记关联实体类型），再分别配置与Vendor、Purchase的关联：

import { Entity, Column, PrimaryGeneratedColumn, ManyToOne, JoinColumn } from 'typeorm';
import { Vendor } from './vendor.entity';
import { Purchase } from './purchase.entity';
import { DocumentType } from './document-type.enum';

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

  // 存储vendor或purchase的id
  @Column()
  documentId: number;

  // 标记当前关联的实体类型
  @Column({
    type: 'enum',
    enum: DocumentType,
  })
  documentType: DocumentType;

  // 关联Vendor
  @ManyToOne(() => Vendor, { nullable: true })
  @JoinColumn({
    name: 'documentId',
    referencedColumnName: 'id',
    foreignKeyConstraintName: 'fk_approval_vendor'
  })
  vendor: Vendor;

  // 关联Purchase
  @ManyToOne(() => Purchase, { nullable: true })
  @JoinColumn({
    name: 'documentId',
    referencedColumnName: 'id',
    foreignKeyConstraintName: 'fk_approval_purchase'
  })
  purchase: Purchase;

  // 其他业务字段...
}

3. 在Vendor和Purchase实体中添加反向关联 #

分别在Vendor和Purchase实体中添加@OneToMany，关联到Approval：

Vendor实体 #

import { Entity, Column, PrimaryGeneratedColumn, OneToMany } from 'typeorm';
import { Approval } from './approval.entity';

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

  // 其他业务字段...

  @OneToMany(() => Approval, approval => approval.vendor)
  approvals: Approval[];
}

Purchase实体 #

import { Entity, Column, PrimaryGeneratedColumn, OneToMany } from 'typeorm';
import { Approval } from './approval.entity';

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

  // 其他业务字段...

  @OneToMany(() => Approval, approval => approval.purchase)
  approvals: Approval[];
}

4. 业务逻辑中的使用方式 #

• 创建关联Vendor的审批：

const targetVendor = await this.vendorRepository.findOneBy({ id: 1 });
const newApproval = this.approvalRepository.create({
  vendor: targetVendor,
  documentType: DocumentType.VENDOR
});
await this.approvalRepository.save(newApproval);

• 创建关联Purchase的审批：

const targetPurchase = await this.purchaseRepository.findOneBy({ id: 1 });
const newApproval = this.approvalRepository.create({
  purchase: targetPurchase,
  documentType: DocumentType.PURCHASE
});
await this.approvalRepository.save(newApproval);

• 查询带关联实体的审批：

// 查询关联Vendor的审批
const vendorApprovals = await this.approvalRepository.find({
  where: { documentType: DocumentType.VENDOR },
  relations: ['vendor']
});

// 查询关联Purchase的审批
const purchaseApprovals = await this.approvalRepository.find({
  where: { documentType: DocumentType.PURCHASE },
  relations: ['purchase']
});

关键说明 #

• PostgreSQL允许同一列存在多个外键约束，这里通过foreignKeyConstraintName指定不同约束名，避免冲突。
• 业务逻辑中要保证approval实例同时只能关联vendor或purchase中的一个，通过documentType标记类型，避免数据混乱。

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