如何在TypeORM中为订单与商品建立含数量的关联关系？

你需要在订单中记录每个产品的购买数量，单纯用@OneToMany关联ProductsEntity无法存储数量信息，而@JoinTable是用于多对多关系的关联表，不支持额外字段，因此不适用。下面提供两种可行方案：

方案一：使用JSON类型字段直接存储（快速实现） #

直接在OrdersEntity中添加一个JSON类型的列，用于存储包含productId和quantity的对象数组。这种方式适合快速落地，但无法利用数据库的关系查询能力，统计和扩展灵活性不足。

修改OrdersEntity： #

import { Entity, PrimaryGeneratedColumn, CreateDateColumn, UpdateDateColumn, Column, ManyToOne } from 'typeorm';
import { UsersEntity } from './users.entity';
import { StatusEnum } from './status.enum';

@Entity('orders')
export class OrdersEntity {
  @PrimaryGeneratedColumn('uuid') id: string;

  @CreateDateColumn() created: Date;

  @UpdateDateColumn() updated: Date;

  @Column('text', { default: StatusEnum.PROCESSING }) status: string;

  // 新增JSON列，存储带数量的产品条目
  @Column({ type: 'json', nullable: false })
  orderItems: Array<{ productId: string; quantity: string }>; // 若需数值类型可改为quantity: number

  // 用户关联
  @ManyToOne(type => UsersEntity, customer => customer.orders)
  customer: UsersEntity;

  // 原Products关联可删除，若需关联查询产品详情可保留
  // @OneToMany(type => ProductsEntity, products => products.order, {cascade: true})
  // products: ProductsEntity[];
}

修改orders.service.ts的create函数： #

async create(data: OrdersDto, userId: string) {
  const { productsArray } = data;
  const user = await this.usersRepository.findOne({ where: { id: userId } });

  // 验证库存并扣减
  for (const productItem of productsArray) {
    const product = await this.productsRepository.findOne({
      where: { id: productItem.productId },
    });
    if (!product) {
      throw new HttpException("Product's ID not found", HttpStatus.NOT_FOUND);
    }
    const quantity = typeof productItem.quantity === 'string' ? parseInt(productItem.quantity, 10) : productItem.quantity;
    if (product.unitsOnStock - quantity < 0) {
      throw new HttpException('Insufficient stock', HttpStatus.BAD_REQUEST);
    }
    await this.productsRepository.update(product.id, {
      unitsOnStock: product.unitsOnStock - quantity,
    });
  }

  // 创建订单并存储产品条目
  const order = await this.ordersRepository.create({
    orderItems: productsArray,
    customer: user,
  });

  await this.ordersRepository.save(order);
  return order;
}

方案二：创建OrderItem中间实体（推荐，符合数据库设计规范） #

通过新增OrderItemEntity中间实体维护订单与产品的关联，同时存储quantity字段，能充分利用TypeORM的关系查询能力，适合复杂业务场景的长期扩展。

1. 创建OrderItemEntity： #

import { Entity, PrimaryGeneratedColumn, Column, ManyToOne, CreateDateColumn } from 'typeorm';
import { OrdersEntity } from './orders.entity';
import { ProductsEntity } from './products.entity';

@Entity('order_items')
export class OrderItemEntity {
  @PrimaryGeneratedColumn('uuid') id: string;

  @CreateDateColumn() created: Date;

  @Column('integer') quantity: number; // 若需字符串类型可改为'text'

  @ManyToOne(type => OrdersEntity, order => order.orderItems, { onDelete: 'CASCADE' })
  order: OrdersEntity;

  @ManyToOne(type => ProductsEntity, product => product.orderItems)
  product: ProductsEntity;
}

2. 修改OrdersEntity： #

import { Entity, PrimaryGeneratedColumn, CreateDateColumn, UpdateDateColumn, Column, ManyToOne, OneToMany } from 'typeorm';
import { UsersEntity } from './users.entity';
import { StatusEnum } from './status.enum';
import { OrderItemEntity } from './order-item.entity';

@Entity('orders')
export class OrdersEntity {
  @PrimaryGeneratedColumn('uuid') id: string;

  @CreateDateColumn() created: Date;

  @UpdateDateColumn() updated: Date;

  @Column('text', { default: StatusEnum.PROCESSING }) status: string;

  // 用户关联
  @ManyToOne(type => UsersEntity, customer => customer.orders)
  customer: UsersEntity;

  // 关联订单条目
  @OneToMany(type => OrderItemEntity, orderItem => orderItem.order, { cascade: true })
  orderItems: OrderItemEntity[];

  // 删除原Products关联
  // @OneToMany(type => ProductsEntity, products => products.order, {cascade: true})
  // products: ProductsEntity[];
}

3. 修改ProductsEntity： #

import { Entity, PrimaryGeneratedColumn, CreateDateColumn, UpdateDateColumn, Column, ManyToOne, OneToMany } from 'typeorm';
import { CategoriesEntity } from './categories.entity';
import { OrderItemEntity } from './order-item.entity';

export class ProductsEntity {
  @PrimaryGeneratedColumn('uuid') id: string;

  @CreateDateColumn() created: Date;

  @UpdateDateColumn() updated: Date;

  @Column({ type: 'text', unique: true }) name: string;

  @Column('text') description: string;

  @Column('integer') unitsOnStock: number;

  @Column('numeric', { precision: 10, scale: 2 }) price: number;

  // 分类关联
  @ManyToOne(type => CategoriesEntity, category => category.products)
  category: CategoriesEntity;

  // 新增订单条目关联
  @OneToMany(type => OrderItemEntity, orderItem => orderItem.product)
  orderItems: OrderItemEntity[];

  // 删除原Order关联
  // @ManyToOne(type => OrdersEntity, order => order.products)
  // order: OrdersEntity;
}

4. 修改orders.service.ts的create函数： #

async create(data: OrdersDto, userId: string) {
  const { productsArray } = data;
  const user = await this.usersRepository.findOne({ where: { id: userId } });
  const orderItems: OrderItemEntity[] = [];

  // 验证库存并创建订单条目
  for (const productItem of productsArray) {
    const product = await this.productsRepository.findOne({
      where: { id: productItem.productId },
    });
    if (!product) {
      throw new HttpException("Product's ID not found", HttpStatus.NOT_FOUND);
    }
    const quantity = typeof productItem.quantity === 'string' ? parseInt(productItem.quantity, 10) : productItem.quantity;
    if (product.unitsOnStock - quantity < 0) {
      throw new HttpException('Insufficient stock', HttpStatus.BAD_REQUEST);
    }
    // 创建订单条目实例
    const orderItem = this.orderItemRepository.create({
      product,
      quantity,
    });
    orderItems.push(orderItem);
    // 扣减库存
    await this.productsRepository.update(product.id, {
      unitsOnStock: product.unitsOnStock - quantity,
    });
  }

  // 创建订单并关联条目
  const order = await this.ordersRepository.create({
    customer: user,
    orderItems,
  });

  await this.ordersRepository.save(order);
  // 返回包含产品详情的订单数据
  return this.ordersRepository.findOne({
    where: { id: order.id },
    relations: ['orderItems', 'orderItems.product'],
  });
}

方案对比 #

• 方案一：实现简单，适合快速开发，但无法利用数据库索引和关系查询，后期统计、修改订单条目灵活性差。
• 方案二：符合关系型数据库设计规范，支持复杂查询和业务扩展，是生产环境的推荐方案。

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