Theo's Typesafe Cult•14mo ago

Good way to do many to many relationship Prisma

I have these schemas where I want to create a many to many relationship between the Xyz table to the subcategory as well as between the Kva table to the subcategory. This is a schema sketch of how I would want to do it but I get the error: Error validating: Embedded many-to-many relations are not supported on MySQL. Please use the syntax defined in https://pris.ly/d/relational-database-many-to-many Is there any way to do it without intermediate tables? Will add up to a lot of tables over time 🙂 model Subcategory { id String @id @default(cuid()) name String xyzs Xyz[] @relation("XyzSubcategory") kvas Kva[] @relation("KvaSubcategory") } model Xyz { id String @id @default(cuid()) questionPrompt String @db.Text ... subcategories Subcategory[] @relation("XyzSubcategory", references: [id], fields: [subcategoryId]) subcategoryId String? } model Kva { id String @id @default(cuid()) questionPrompt String @db.Text ... subcategories Subcategory[] @relation("KvaSubcategory", references: [id], fields: [subcategoryId]) subcategoryId String? }

Prisma

Many-to-many relations

How to define and work with many-to-many relations in Prisma.

1 Reply

ja_iy•14mo ago

OwO7 from what i know there are 2 main ways to define many to many relations in prisma Implicit relation table (prisma will create the relation table for you)

model Subcategory {
  id    String  @id @default(cuid())
  name  String
  xyzs  Xyz[]   //@relation("CustomRelationTableName") 
  kvas  Kva[]   
}

model Xyz {
  id             String          @id @default(cuid())
  questionPrompt String          @db.Text
  subcategories  Subcategory[]  //@relation("CustomRelationTableName") 
}

model Kva {
  id             String          @id @default(cuid())
  questionPrompt String          @db.Text
  subcategories  Subcategory[]   
}

model Subcategory {
  id    String  @id @default(cuid())
  name  String
  xyzs  Xyz[]   //@relation("CustomRelationTableName") 
  kvas  Kva[]   
}

model Xyz {
  id             String          @id @default(cuid())
  questionPrompt String          @db.Text
  subcategories  Subcategory[]  //@relation("CustomRelationTableName") 
}

model Kva {
  id             String          @id @default(cuid())
  questionPrompt String          @db.Text
  subcategories  Subcategory[]   
}

Explicit relation table (you define the relation table, allows for custom fields on relation)

model Subcategory {
    id   String                @id @default(cuid())
    name String
    xyzs Subcategory_Xyz_Rel[]
    kvas Subcategory_Kva_Rel[]
}

model Xyz {
    id                  String                @id @default(cuid())
    questionPrompt      String                @db.Text
    subcategories       Subcategory_Xyz_Rel[]
}

model Kva {
    id             String                @id @default(cuid())
    questionPrompt String                @db.Text
    subcategories  Subcategory_Kva_Rel[]
}

model Subcategory_Xyz_Rel {
    id            String      @id @default(cuid())
    subcategoryId String
    xyzId         String
    subcategory   Subcategory @relation(fields: [subcategoryId], references: [id], onDelete: Cascade)
    xyz           Xyz         @relation(fields: [xyzId], references: [id], onDelete: Cascade)
    // add custom relation fields here
}

model Subcategory_Kva_Rel {
    id            String      @id @default(cuid())
    subcategoryId String
    subcategory   Subcategory @relation(fields: [subcategoryId], references: [id], onDelete: Cascade)
    Kva           Kva?        @relation(fields: [kvaId], references: [id])
    kvaId         String?
    // add custom relation fields here
}

model Subcategory {
    id   String                @id @default(cuid())
    name String
    xyzs Subcategory_Xyz_Rel[]
    kvas Subcategory_Kva_Rel[]
}

model Xyz {
    id                  String                @id @default(cuid())
    questionPrompt      String                @db.Text
    subcategories       Subcategory_Xyz_Rel[]
}

model Kva {
    id             String                @id @default(cuid())
    questionPrompt String                @db.Text
    subcategories  Subcategory_Kva_Rel[]
}

model Subcategory_Xyz_Rel {
    id            String      @id @default(cuid())
    subcategoryId String
    xyzId         String
    subcategory   Subcategory @relation(fields: [subcategoryId], references: [id], onDelete: Cascade)
    xyz           Xyz         @relation(fields: [xyzId], references: [id], onDelete: Cascade)
    // add custom relation fields here
}

model Subcategory_Kva_Rel {
    id            String      @id @default(cuid())
    subcategoryId String
    subcategory   Subcategory @relation(fields: [subcategoryId], references: [id], onDelete: Cascade)
    Kva           Kva?        @relation(fields: [kvaId], references: [id])
    kvaId         String?
    // add custom relation fields here
}

Gaming

Programming

Good way to do many to many relationship Prisma