如何在Prisma多对多关联表中存储商品历史售价无需预查价格？

问题描述 #

我有一个包含Product、Order模型及二者多对多关联的Prisma Schema。由于商品价格随时可能变动，希望在关联表ProductsOnOrder中存储商品下单时的售价，查询订单数据时获取历史售价而非当前价格。请问能否在Prisma中实现这一需求且无需预先查询商品价格？

我的Schema如下：

model Product {
  id          String            @id @default(cuid())
  name        String
  // todo add check stock can't be less than zero
  stock       Int
  // todo add check price can't be less than zero
  buyPrice    Float
  sellPrice   Float
  image       String
  createdAt   DateTime          @default(now())
  createdBy   User              @relation(fields: [createdById], references: [id])
  createdById String
  category    Category          @relation(fields: [categoryId], references: [id])
  categoryId  String
  orders      ProductsOnOrder[]
}

model Order {
  id          String            @id @default(cuid())
  // guess it's suppose to be computed value
  total       Float
  paymentType PaymentType       @default(CASH)
  createdAt   DateTime          @default(now())
  createdById String
  createdBy   User              @relation(fields: [createdById], references: [id])
  products    ProductsOnOrder[]
}

model ProductsOnOrder {
  productId String
  Product   Product @relation(fields: [productId], references: [id])
  orderId   String
  order     Order   @relation(fields: [orderId], references: [id])
  quantity  Int     @default(1)
  price     Float // Price of the product at the time of the order

  @@id([productId, orderId])
}

实现方案 #

完全可以实现，且无需在业务层预先查询商品价格，以下是两种可靠的实现方式：

1. Prisma事务+批量关联创建（业务层控制）

利用Prisma事务的原子性，在创建订单的同时批量获取商品当前售价并填充到关联表，避免价格在查询与插入之间变动。

代码示例：

async function createOrder(userId: string, items: { productId: string; quantity: number }[]) {
  return prisma.$transaction(async (tx) => {
    // 事务内批量获取目标商品的当前售价
    const products = await tx.product.findMany({
      where: { id: { in: items.map(i => i.productId) } },
      select: { id: true, sellPrice: true }
    });

    // 构建关联表数据，自动绑定当前售价
    const orderItems = items.map(item => {
      const targetProduct = products.find(p => p.id === item.productId);
      if (!targetProduct) throw new Error(`商品 ${item.productId} 不存在`);
      return {
        productId: item.productId,
        quantity: item.quantity,
        price: targetProduct.sellPrice
      };
    });

    // 创建订单并关联商品
    const newOrder = await tx.order.create({
      data: {
        createdById: userId,
        products: { create: orderItems }
      },
      include: { products: { include: { Product: true } } }
    });

    // 自动计算并更新订单总价
    const totalAmount = orderItems.reduce((sum, item) => sum + item.price * item.quantity, 0);
    await tx.order.update({
      where: { id: newOrder.id },
      data: { total: totalAmount }
    });

    return newOrder;
  });
}

2. 数据库触发器自动填充（底层自动处理）

通过数据库触发器，在插入ProductsOnOrder记录时自动从Product表获取当前售价填充到price字段，完全无需业务层干预，所有插入场景都能生效。

步骤：

1. 在数据库中创建触发器函数和触发器（以PostgreSQL为例）：

-- 创建触发器函数：获取商品当前售价
CREATE OR REPLACE FUNCTION set_order_product_price()
RETURNS TRIGGER AS $$
BEGIN
  SELECT sellPrice INTO NEW.price
  FROM "Product"
  WHERE id = NEW.productId;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器：插入ProductsOnOrder前自动设置价格
CREATE TRIGGER trigger_fill_order_product_price
BEFORE INSERT ON "ProductsOnOrder"
FOR EACH ROW
EXECUTE FUNCTION set_order_product_price();

2. （可选）在Prisma Schema中通过@@raw语句定义触发器，确保数据库迁移时自动创建：

model ProductsOnOrder {
  productId String
  Product   Product @relation(fields: [productId], references: [id])
  orderId   String
  order     Order   @relation(fields: [orderId], references: [id])
  quantity  Int     @default(1)
  price     Float // Price of the product at the time of the order

  @@id([productId, orderId])
  @@raw(`
    CREATE OR REPLACE FUNCTION set_order_product_price()
    RETURNS TRIGGER AS $$
    BEGIN
      SELECT sellPrice INTO NEW.price
      FROM "Product"
      WHERE id = NEW.productId;
      RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;

    CREATE TRIGGER trigger_fill_order_product_price
    BEFORE INSERT ON "ProductsOnOrder"
    FOR EACH ROW
    EXECUTE FUNCTION set_order_product_price();
  `)
}

之后创建订单时，只需指定productId和quantity，price会自动填充：

async function createOrder(userId: string, items: { productId: string; quantity: number }[]) {
  return prisma.$transaction(async (tx) => {
    const newOrder = await tx.order.create({
      data: {
        createdById: userId,
        products: {
          create: items.map(item => ({
            productId: item.productId,
            quantity: item.quantity
          }))
        }
      },
      include: { products: { include: { Product: true } } }
    });

    // 自动计算总价
    const totalAmount = newOrder.products.reduce((sum, item) => sum + item.price * item.quantity, 0);
    await tx.order.update({
      where: { id: newOrder.id },
      data: { total: totalAmount }
    });

    return newOrder;
  });
}

补充：完善Schema约束 #

你Schema中的todo项可以通过Prisma的@@check约束实现：

model Product {
  id          String            @id @default(cuid())
  name        String
  stock       Int
  buyPrice    Float
  sellPrice   Float
  image       String
  createdAt   DateTime          @default(now())
  createdBy   User              @relation(fields: [createdById], references: [id])
  createdById String
  category    Category          @relation(fields: [categoryId], references: [id])
  categoryId  String
  orders      ProductsOnOrder[]

  // 添加数值合法性检查
  @@check([stock >= 0, buyPrice >= 0, sellPrice >= 0])
}

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