如何在Prisma中实现多对多关系的数据批量插入？

我曾尝试使用connect和connectOrCreate方法，但未能成功实现需求。我的需求是：每次插入采购记录时，可能会关联多个产品，希望传入包含这些产品的数组/JSON数据，一次性完成采购、产品及中间关联表的插入操作。

模型定义 #

model Category {
  id      Int       @id @default(autoincrement())
  name    String    @unique @db.VarChar(50)
  product Product[]

  @@map("categories")
}

model Product {
  id         Int         @id @default(autoincrement())
  name       String      @unique @db.VarChar(35)
  barcode    String      @unique @db.VarChar(35)
  sell       Decimal     @db.Decimal(12, 2)
  shop       Decimal     @db.Decimal(12, 2)
  quantity   Int
  expiresIn  String
  createdAt  DateTime    @default(now())
  updatedAt  DateTime    @updatedAt
  categoryId Int
  category   Category    @relation(fields: [categoryId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  sales      SaleOrder[]
  purchases  PurchaseProduct[]
  
  @@map("products")
}

model PurchaseProduct{
  name       String    @db.VarChar(50)
  sell       Decimal   @db.Decimal(12, 2)
  shop       Decimal   @db.Decimal(12, 2)
  quantity   Int
  expiresIn  String
  purchaseId Int
  purchase   Purchase  @relation(fields: [purchaseId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  productId  Int
  product    Product   @relation(fields: [productId], references: [id], onDelete: Cascade, onUpdate: Cascade)

  @@id([purchaseId, productId])

  @@map("purchaseProducts")
}

model Purchase{
  id         Int     @id @default(autoincrement())
  supplierId Int
  supplier   Supplier @relation(fields: [supplierId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt

  purchases  PurchaseProduct[]
  @@map("purchases")
}

model Supplier {
  id          Int       @id @default(autoincrement())
  name        String    @unique @db.VarChar(35)
  email       String?   @unique @db.VarChar(35)
  contact     String?   @unique @db.VarChar(10)
  products    Purchase[]

  @@map("suppliers")
}

尝试过的代码 #

export async function create(formData: Purchase){
  const purchase = await db.purchase.create({
    data:   {
      name: formData.name,
      sell: formData.sell,
      shop: formData.shop,
      quantity: formData.quantity,
      supplier: {
                  connectOrCreate:{
                    where:{name: formData.supplierName},
                    create:{name: formData.supplierName, contact: formData.phone, email: formData.email}
                }
              },
      product:{
                connectOrCreate:{where:{name:formData.name}, 
                create:{
                      name: FormData.name,
                      barcode: formData.barcode,
                      sell:formData.sell,
                      shop: formData.shop,
                      quantity: formData.quantity,
                      expiresIn: formData.expiresIn,
                      categoryId: formData.categoryId
                    }}
                }}
              })
              
  return purchase;
}

核心问题是未正确利用Prisma多对多关联的嵌套写入语法，且原代码仅支持单个产品处理，未适配数组传入场景。以下是修正后的实现：

首先调整入参类型，确保包含产品数组，再通过purchases字段（对应中间表PurchaseProduct）批量创建关联关系，同时对每个产品执行connectOrCreate：

// 定义符合需求的入参类型（可根据实际业务调整）
interface PurchaseCreateInput {
  supplierName: string;
  phone?: string;
  email?: string;
  products: Array<{
    name: string;
    barcode: string;
    sell: number;
    shop: number;
    quantity: number;
    expiresIn: string;
    categoryId: number;
    // 中间表自身需要的字段
    purchaseName: string;
    purchaseSell: number;
    purchaseShop: number;
    purchaseQuantity: number;
    purchaseExpiresIn: string;
  }>;
}

export async function create(formData: PurchaseCreateInput) {
  const purchase = await db.purchase.create({
    data: {
      // 供应商关联逻辑保留
      supplier: {
        connectOrCreate: {
          where: { name: formData.supplierName },
          create: {
            name: formData.supplierName,
            contact: formData.phone,
            email: formData.email
          }
        }
      },
      // 批量处理产品与中间表的关联
      purchases: {
        create: formData.products.map(product => ({
          // 填充中间表自身字段
          name: product.purchaseName,
          sell: product.purchaseSell,
          shop: product.purchaseShop,
          quantity: product.purchaseQuantity,
          expiresIn: product.purchaseExpiresIn,
          // 处理产品的连接或创建
          product: {
            connectOrCreate: {
              where: { name: product.name }, // 用唯一字段匹配已有产品
              create: {
                name: product.name,
                barcode: product.barcode,
                sell: product.sell,
                shop: product.shop,
                quantity: product.quantity,
                expiresIn: product.expiresIn,
                categoryId: product.categoryId
              }
            }
          }
        }))
      }
    },
    // 可选：返回关联数据便于验证结果
    include: {
      supplier: true,
      purchases: {
        include: { product: true }
      }
    }
  });

  return purchase;
}

关键说明 #

• 中间表操作逻辑：Purchase与Product通过PurchaseProduct中间表关联，需操作Purchase的purchases字段创建中间表记录，而非直接操作product字段。
• 数组批量处理：使用map将传入的产品数组转换为Prisma要求的create数组，实现一次性批量插入。
• 字段匹配：确保中间表的自有字段正确赋值，同时保留产品的connectOrCreate逻辑，保证产品不存在时自动创建。

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