如何在Prisma中实现0对多关系并解决create报错问题

问题 #

开发每日营养系统时，需要实现InfoNutriDay的三种关联场景：

• 关联多个Food，无Meal
• 关联多个Meal，无Food
• 同时关联多个Food和Meal

已手动创建中间表完成数据库建模，但使用prisma.create创建数据时出现错误：Argument connect: Invalid value provided. Expected FoodWhereUniqueInput, provided (Object)

当前模型代码 #

model infonutriday {
  id                     String                   @id @db.VarChar(145)
  date                   DateTime                 @db.Date
  portion                Float                    @db.Float
  protein                Float                    @db.Float
  calories               Float                    @db.Float
  grease                 Float                    @db.Float
  salt                   Float                    @db.Float
  finalizedDay           Int                      @db.TinyInt

  infonutriday_has_meals infonutriday_has_meals[]
  infonutriday_has_foods infonutriday_has_foods[]
  infonutriday_has_users infonutriday_has_users[]
}

model infonutriday_has_meals {
  infonutriday_id String       @db.VarChar(45)
  infonutriday    infonutriday @relation(fields: [infonutriday_id], references: [id])

  meals_id Int
  meals    Meal @relation(fields: [meals_id], references: [id])

  @@id([infonutriday_id, meals_id])
}

model infonutriday_has_foods {
  infonutriday_id String
  infonutriday    infonutriday @relation(fields: [infonutriday_id], references: [id])

  foods_id Int
  foods    Food @relation(fields: [foods_id], references: [id])

  @@id([infonutriday_id, foods_id])
}

model infonutriday_has_users {
  infonutriday_id String       @db.VarChar(45)
  infonutriday    infonutriday @relation(fields: [infonutriday_id], references: [id])

  users_id Int
  users    User @relation(fields: [users_id], references: [id])

  @@id([infonutriday_id, users_id])
}

报错时使用的prisma.create代码片段 #

infonutriday_has_foods: foods_id ? {
                    create: {
                        foods: {
                            connect: foods_id.map((foodId: number) => (
                                {
                                    id: foodId,
                                }
                            ))
                        },
                    },
                } : undefined,

                infonutriday_has_meals: meals_id ? {
                    create: {
                        meals: {
                            connect: meals_id.map((mealId: number) => (
                                {
                                    id: mealId,
                                }
                            ))

                        },
                    },
                } : undefined,

完整报错详情 #

const newInfoNutriDay = await prisma.infonutriday.create({
        data: {
          id: "04-ago-2023+1696526834318",
          date: "2023-08-03T22:00:00.000Z",
          portion: 33,
          protein: 33,
          calories: 33,
          grease: 33,
          salt: 3,
          finalizedDay: 0,
          infonutriday_has_foods: {
            create: {
              foods: {
                connect: [
                  {
                    id: 1
                  }
                ]
              }
            }
          },
          infonutriday_has_meals: {
            create: {
              meals: {
                connect: [
                  {
                    id: 4
                  },
                  {
                    id: 3
                  }
                ]
              }
            }
          },
          infonutriday_has_users: {
            create: {
              users: {
                connect: {
                  id: 1
                }
              }
            }
          }
        },
        include: {
          infonutriday_has_foods: {
            include: {
              foods: true
            }
          },
          infonutriday_has_meals: {
            include: {
              meals: true
            }
          }
        }
      })

Argument `connect`: Invalid value provided. Expected FoodWhereUniqueInput, provided (Object).

错误原因 #

你在创建关联时犯了两个核心错误：

• 多关联创建方式错误：infonutriday_has_foods和infonutriday_has_meals是多对多中间表，每个中间表实例只能关联一个Food/Meal和一个InfoNutriDay。你试图在单个create对象里给foods.connect传数组，但connect接受的是单个唯一条件对象，而非数组。
• 批量创建语法错误：要关联多个Food/Meal，需要给infonutriday_has_foods.create传对象数组，每个元素对应一个独立的中间表关联实例，而非单个对象。

解决方案 #

1. 修正现有模型的prisma.create代码 #

针对多关联场景，将create的值改为数组，每个数组元素对应一个中间表实例，分别关联单个Food/Meal：

const newInfoNutriDay = await prisma.infonutriday.create({
  data: {
    id: "04-ago-2023+1696526834318",
    date: "2023-08-03T22:00:00.000Z",
    portion: 33,
    protein: 33,
    calories: 33,
    grease: 33,
    salt: 3,
    finalizedDay: 0,
    // 关联多个Food：创建多个中间表实例
    infonutriday_has_foods: {
      create: [
        { foods: { connect: { id: 1 } } },
        // 可继续添加更多Food关联
      ]
    },
    // 关联多个Meal：创建多个中间表实例
    infonutriday_has_meals: {
      create: [
        { meals: { connect: { id: 4 } } },
        { meals: { connect: { id: 3 } } }
      ]
    },
    infonutriday_has_users: {
      create: {
        users: { connect: { id: 1 } }
      }
    }
  },
  include: {
    infonutriday_has_foods: { include: { foods: true } },
    infonutriday_has_meals: { include: { meals: true } }
  }
})

2. 优化模型定义（可选但推荐） #

Prisma支持自动生成多对多中间表，无需手动定义infonutriday_has_foods这类表，可大幅简化模型和关联代码：

model InfoNutriDay {
  id           String   @id @db.VarChar(145)
  date         DateTime @db.Date
  portion      Float    @db.Float
  protein      Float    @db.Float
  calories     Float    @db.Float
  grease       Float    @db.Float
  salt         Float    @db.Float
  finalizedDay Int      @db.TinyInt

  // 自动生成多对多中间表
  foods Food[] @relation("InfoNutriDayFoods")
  meals Meal[] @relation("InfoNutriDayMeals")
  users User[] @relation("InfoNutriDayUsers")
}

model Food {
  id             Int           @id @default(autoincrement())
  // 其他Food字段...
  infoNutriDays  InfoNutriDay[] @relation("InfoNutriDayFoods")
}

model Meal {
  id             Int           @id @default(autoincrement())
  // 其他Meal字段...
  infoNutriDays  InfoNutriDay[] @relation("InfoNutriDayMeals")
}

model User {
  id             Int           @id @default(autoincrement())
  // 其他User字段...
  infoNutriDays  InfoNutriDay[] @relation("InfoNutriDayUsers")
}

使用简化后的模型，创建关联的代码会更简洁：

const newInfoNutriDay = await prisma.infoNutriDay.create({
  data: {
    id: "04-ago-2023+1696526834318",
    date: "2023-08-03T22:00:00.000Z",
    portion: 33,
    protein: 33,
    calories: 33,
    grease: 33,
    salt: 3,
    finalizedDay: 0,
    // 直接关联多个Food
    foods: { connect: [{ id: 1 }] },
    // 直接关联多个Meal
    meals: { connect: [{ id: 4 }, { id: 3 }] },
    users: { connect: [{ id: 1 }] }
  },
  include: { foods: true, meals: true, users: true }
})

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