Theo's Typesafe Cult•2y ago

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
}

Basically. Every User should be able to have several ShoppingLists. And the ShoppingLists can contain several Items.

Solution:

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

Jump to solution

5 Replies

Sybatron•2y ago

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[]
}

just fix the item model

Solution

Sybatron•2y ago

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

Sybatron•2y ago

here you have many to many

Jazon•2y ago

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?

Sybatron•2y ago

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

Gaming

Programming

Handle many-to-many relations in Prisma with MySQL