Handle many-to-many relations in Prisma with MySQL

I am struggling with creating a schema that works in Prisma with MySQL. I've read an article at prisma.io regarding the subject, but couldn't figure out how to solve my own problem.

Here's the schema:

model User {
  id String @id @default(cuid())
  name String
  shoppingList ShoppingList[]
}

model ShoppingList {
  id String @id @default(cuid())
  name String
  items Item[]
  user User @relation(fields: [userId], references: [id])
  userId String
}

model Item {
  id String @id @default(cuid())
  name String
  productName String
  category String
  shoppingList ShoppingList[] @relation(fields: [shoppingId], references: [id])
  shoppingId String
}

model User {
  id String @id @default(cuid())
  name String
  shoppingList ShoppingList[]
}

model ShoppingList {
  id String @id @default(cuid())
  name String
  items Item[]
  user User @relation(fields: [userId], references: [id])
  userId String
}

model Item {
  id String @id @default(cuid())
  name String
  productName String
  category String
  shoppingList ShoppingList[] @relation(fields: [shoppingId], references: [id])
  shoppingId String
}

model User {
  id String @id @default(cuid())
  name String
  shoppingList ShoppingList[]
}

model ShoppingList {
  id String @id @default(cuid())
  name String
  items Item[]
  user User @relation(fields: [userId], references: [id])
  userId String
}

model Item {
  id String @id @default(cuid())
  name String
  productName String
  category String
  shoppingList ShoppingList[] @relation(fields: [shoppingId], references: [id])
  shoppingId String
}

model User {
  id String @id @default(cuid())
  name String
  shoppingList ShoppingList[]
}

model ShoppingList {
  id String @id @default(cuid())
  name String
  items Item[]
  user User @relation(fields: [userId], references: [id])
  userId String
}

model Item {
  id String @id @default(cuid())
  name String
  productName String
  category String
  shoppingList ShoppingList[] @relation(fields: [shoppingId], references: [id])
  shoppingId String
}

Basically. Every

User

User

User

User should be able to have several

ShoppingLists

ShoppingLists

ShoppingLists

ShoppingLists. And the

ShoppingLists

ShoppingLists

ShoppingLists

ShoppingLists can contain several

Items

Items

Items

Items.

Solution

you use @relation and add id when you have one to one or one to many

Jump to solution

Sybatron•4/23/23, 6:57 PM

model Item {
    id           String         @id @default(cuid())
    name         String
    productName  String
    category     String
    shoppingList ShoppingList[]
}

model Item {
    id           String         @id @default(cuid())
    name         String
    productName  String
    category     String
    shoppingList ShoppingList[]
}

Sybatron•4/23/23, 6:58 PM

just fix the item model

Solution

Sybatron•4/23/23, 6:58 PM

you use @relation and add id when you have one to one or one to many

Sybatron•4/23/23, 6:58 PM

here you have many to many

Chill GuyOP•4/23/23, 7:10 PM

Ohh okay, cool! Any explanation to why it works like this? Why do you need relation when it's not many-to-many and vice versa?

CChill Guy Ohh okay, cool! Any explanation to why it works like this? Why do you need relat...

Sybatron•4/23/23, 7:18 PM

Saying shoppingList[] and Item[] creates many to many relation in PrismaClient
but for models that are pointing to only one object in a model aka one-to-many and one-to-one
we need to use @relation to create the relation in PrismaClient by saying which field is FK that connects this model to the other model

Handle many-to-many relations in Prisma with MySQL

Similar Threads

Similar Threads

Similar Threads