How can I create a list of nested records using Prisma that have a relation to a different table?

Question

I'm trying Prisma for the first time (and backend in general) and am creating a recipe/meal planning app where a user can add recipes to a meal plan then that generates a shopping list for them. Here is the Prisma schema I am using for this:

model MealPlan {
  id           String       @id @default(cuid())
  createdAt    DateTime     @default(now())
  title        String
  description  String?
  authorId     String
  likedBy      String[]
  recipes      Recipe[]     @relation("mealPlanRecipe")
  // use the meal plan id as the shopping list id for each meal plan, each meal plan will have its own unique shopping list
  shoppingList ShoppingList @relation("mealPlanShoppingList", fields: [id], references: [id])

  @@index([authorId])
}

model Recipe {
  id           String             @id @default(cuid())
  createdAt    DateTime           @default(now())
  authorId     String?
  title        String
  subtitle     String?
  description  String
  imageUrl     String?
  calories     Int?
  slug         String
  cookTime     Int?
  prepTime     Int?
  recipeSource String?
  likedBy      String[]
  instructions String             @default("")
  recipeTags   RecipeTag[]        @relation("recipeTag")
  mealPlan     MealPlan[]         @relation("mealPlanRecipe")
  ingredients  RecipeIngredient[]
}

model RecipeTag {
  id      String   @id @default(cuid())
  name    String
  recipes Recipe[] @relation("recipeTag")
}

model ShoppingList {
  id          String                   @id @default(cuid())
  createdAt   DateTime                 @default(now())
  ingredients ShoppingListIngredient[] @relation("shoppingList")
  userId      String
  mealPlan    MealPlan?                @relation("mealPlanShoppingList")
}

model ShoppingListIngredient {
  shoppingList   ShoppingList      @relation("shoppingList", fields: [shoppingListId], references: [id], map: "shoppingList")
  shoppingListId String
  ingredient     IngredientDetails @relation("shoppingListIngredient", fields: [shoppingListId], references: [id])
  ingredientId   String
  quantity       Float

  @@id([shoppingListId, ingredientId])
}

model RecipeIngredient {
  recipe       Recipe            @relation(fields: [recipeId], references: [id])
  recipeId     String
  ingredient   IngredientDetails @relation(fields: [ingredientId], references: [id])
  ingredientId String
  quantity     Float

  @@id([recipeId, ingredientId])
}

model IngredientDetails {
  id            String                   @id @default(cuid())
  createdAt     DateTime                 @default(now())
  name          String
  unit          String
  shoppingLists ShoppingListIngredient[] @relation("shoppingListIngredient")
  recipes       RecipeIngredient[]
}

And I am trying to create the meal plan like so in my TRPC router:

const mealPlan = await ctx.prisma.mealPlan.create({
  data: {
    createdAt: new Date(),
    authorId: authorId,
    title: input.title,
    description: input.description,
    shoppingList: {
      create: {
        userId: authorId,
        ingredients: {
          create: shoppingList.map((item) => ({
            ingredient: {
              connect: {
                id: item.ingredientId,
              },
            },
            ingredientId: item.ingredientId,
            quantity: item.quantity,
          })),
        },
      },
    },
    recipes: {
      connect: input.recipes.map((id) => ({
        id,
      })),
    },
  },
  include: {
    shoppingList: {
      include: {
        ingredients: true,
      },
    },
  },
});

I'm getting an error that says:

Foreign key constraint failed on the field: ShoppingListIngredient_shoppingListId_fkey (index)

My goal is when creating the meal plan to then also create the meal plan's shopping list and create the shoppingListIngredients inside that shopping list. The reason I want a shoppingListIngredients table is so that users can be able to mark ingredients as "bought" as they're shopping but I'm having trouble getting this create to work. I'm not sure if this is an issue with the Prisma schema I have set up or I'm just misunderstanding how nested creates work. Would really appreciate a nudge in the right direction.

Answer 1

It was an issue with the schema—I was referencing the shoppingListId in the shoppingListIngredient relation instead of the ingredientId

The ShoppingListIngredient model should have been:

model ShoppingListIngredient {
  shoppingList   ShoppingList      @relation("shoppingList", fields: [shoppingListId], references: [id], map: "shoppingList")
  shoppingListId String
  ingredient     IngredientDetails @relation("shoppingListIngredient", fields: [ingredientId], references: [id])
  ingredientId   String
  quantity       Float

  @@id([shoppingListId, ingredientId])
}