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
ja_iy•2y 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
}
Want results from more Discord servers?
Add your server