P
Prisma2mo ago
Sky✧

Implicite Many-to-Many relationship double primary key

Implicite Many-to-Many relationship with two models, but one of these two models has a double primary key?
Solution:
Here is an example of how you can define an explicit many-to-many relation based off of your example ``` model Kit { kitid Int @id @default(autoincrement()) kitname String...
Jump to solution
4 Replies
Sky✧
Sky✧2mo ago
model Kit {
kitid Int @id @default(autoincrement())
kitname String
guildid BigInt
guild Guild @relation(fields: [guildid], references: [guildid])
channelid BigInt?
enable Boolean @default(false)
kitrows KitRow[]
adminroles Role[] @relation(name: "adminrole")
requiredroles Role[] @relation(name: "requiredrole")

@@unique([kitname, guildid])
}

model Role {
roleid BigInt
guildid BigInt
guild Guild @relation(fields: [guildid], references: [guildid])
kitidadminrole Int?
kitadminrole Kit? @relation(name: "adminrole", fields: [kitidadminrole], references: [kitid])
kitidrequiredrole Int?
kitrequeriedrole Kit? @relation(name: "requiredrole",fields: [kitidrequiredrole], references: [kitid])

@@id([roleid, guildid])
}
model Kit {
kitid Int @id @default(autoincrement())
kitname String
guildid BigInt
guild Guild @relation(fields: [guildid], references: [guildid])
channelid BigInt?
enable Boolean @default(false)
kitrows KitRow[]
adminroles Role[] @relation(name: "adminrole")
requiredroles Role[] @relation(name: "requiredrole")

@@unique([kitname, guildid])
}

model Role {
roleid BigInt
guildid BigInt
guild Guild @relation(fields: [guildid], references: [guildid])
kitidadminrole Int?
kitadminrole Kit? @relation(name: "adminrole", fields: [kitidadminrole], references: [kitid])
kitidrequiredrole Int?
kitrequeriedrole Kit? @relation(name: "requiredrole",fields: [kitidrequiredrole], references: [kitid])

@@id([roleid, guildid])
}
I need to create an implicit many-to-many relationship for simplicity, but one of my two models has a double primary key. I could do an explicit relationship, but I can't figure out how it works.
RaphaelEtim
RaphaelEtim2mo ago
Hi @Sky✧ 👋 You can't use a multi-field id when working with implicit many-to-many relations. See this section of the documentation. You will need to use an explicit many-to-many relation. This involves creating a separate model to act as the relation table. See the documentation on how to define an explicit many-to-many relation.
Many-to-many relations | Prisma Documentation
How to define and work with many-to-many relations in Prisma.
Solution
RaphaelEtim
RaphaelEtim2mo ago
Here is an example of how you can define an explicit many-to-many relation based off of your example
model Kit {
kitid Int @id @default(autoincrement())
kitname String
guildid BigInt
guild Guild @relation(fields: [guildid], references: [guildid])
channelid BigInt?
enable Boolean @default(false)
kitrows KitRow[]
adminroles KitRole[] @relation(name: "adminrole")
requiredroles KitRole[] @relation(name: "requiredrole")

@@unique([kitname, guildid])
}

model Role {
roleid BigInt
guildid BigInt
guild Guild @relation(fields: [guildid], references: [guildid])
kitroles KitRole[]

@@id([roleid, guildid])
}

model KitRole {
kitid Int
roleid BigInt
guildid BigInt
kit Kit @relation(fields: [kitid], references: [kitid])
role Role @relation(fields: [roleid, guildid], references: [roleid, guildid])

@@id([kitid, roleid, guildid])
}
model Kit {
kitid Int @id @default(autoincrement())
kitname String
guildid BigInt
guild Guild @relation(fields: [guildid], references: [guildid])
channelid BigInt?
enable Boolean @default(false)
kitrows KitRow[]
adminroles KitRole[] @relation(name: "adminrole")
requiredroles KitRole[] @relation(name: "requiredrole")

@@unique([kitname, guildid])
}

model Role {
roleid BigInt
guildid BigInt
guild Guild @relation(fields: [guildid], references: [guildid])
kitroles KitRole[]

@@id([roleid, guildid])
}

model KitRole {
kitid Int
roleid BigInt
guildid BigInt
kit Kit @relation(fields: [kitid], references: [kitid])
role Role @relation(fields: [roleid, guildid], references: [roleid, guildid])

@@id([kitid, roleid, guildid])
}
In this schema, KitRole acts as the relation table between Kit and Role. The @@id([kitid, roleid, guildid]) in KitRole ensures that the combination of kitid, roleid, and guildid is unique. This allows you to store additional fields on the relation if needed and properly handles the composite primary key in the Role model.
Sky✧
Sky✧2mo ago
Hawo, I tried again today but when copying your code I get errors because of my double relations in Kit. Errors :
Error validating field `adminroles` in model `Kit`: The relation field `adminroles` on model `Kit` is missing an opposite relation field on the model `KitRole`. Either run `prisma format` or add it manually.Prisma
Error validating field `adminroles` in model `Kit`: The relation field `adminroles` on model `Kit` is missing an opposite relation field on the model `KitRole`. Either run `prisma format` or add it manually.Prisma
Error validating field `requiredroles` in model `Kit`: The relation field `requiredroles` on model `Kit` is missing an opposite relation field on the model `KitRole`. Either run `prisma format` or add it manually.Prisma
Error validating field `requiredroles` in model `Kit`: The relation field `requiredroles` on model `Kit` is missing an opposite relation field on the model `KitRole`. Either run `prisma format` or add it manually.Prisma
Error validating field `kit` in model `KitRole`: The relation field `kit` on model `KitRole` is missing an opposite relation field on the model `Kit`. Either run `prisma format` or add it manually.Prisma
Error validating field `kit` in model `KitRole`: The relation field `kit` on model `KitRole` is missing an opposite relation field on the model `Kit`. Either run `prisma format` or add it manually.Prisma
Where ?
model Kit {
kitid Int @id @default(autoincrement())
kitname String
guildid BigInt
guild Guild @relation(fields: [guildid], references: [guildid])
channelid BigInt?
enable Boolean @default(false)
kitrows KitRow[]
adminroles KitRole[] @relation(name: "adminrole") < HERE
requiredroles KitRole[] @relation(name: "requiredrole") < HERE

@@unique([kitname, guildid])
}

model Role {
roleid BigInt
guildid BigInt
guild Guild @relation(fields: [guildid], references: [guildid])
kitroles KitRole[]

@@id([roleid, guildid])
}

model KitRole {
kitid Int
roleid BigInt
guildid BigInt
kit Kit @relation(fields: [kitid], references: [kitid]) < HERE
role Role @relation(fields: [roleid, guildid], references: [roleid, guildid])

@@id([kitid, roleid, guildid])
}
model Kit {
kitid Int @id @default(autoincrement())
kitname String
guildid BigInt
guild Guild @relation(fields: [guildid], references: [guildid])
channelid BigInt?
enable Boolean @default(false)
kitrows KitRow[]
adminroles KitRole[] @relation(name: "adminrole") < HERE
requiredroles KitRole[] @relation(name: "requiredrole") < HERE

@@unique([kitname, guildid])
}

model Role {
roleid BigInt
guildid BigInt
guild Guild @relation(fields: [guildid], references: [guildid])
kitroles KitRole[]

@@id([roleid, guildid])
}

model KitRole {
kitid Int
roleid BigInt
guildid BigInt
kit Kit @relation(fields: [kitid], references: [kitid]) < HERE
role Role @relation(fields: [roleid, guildid], references: [roleid, guildid])

@@id([kitid, roleid, guildid])
}
I think I need 2 join tables
// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

// Looking for ways to speed up your queries, or scale easily with your serverless or edge functions?
// Try Prisma Accelerate: https://pris.ly/cli/accelerate-init

generator client {
provider = "prisma-client-js"
}

datasource db {
provider = "sqlite"
url = "file:dev.db"
}

model Guild {
guildid BigInt @id
joinedAt DateTime @default(now())
prenium Boolean @default(false)
kits Kit[]
roles Role[]
}

model Kit {
kitid Int @id @default(autoincrement())
kitname String
guildid BigInt
guild Guild @relation(fields: [guildid], references: [guildid])
channelid BigInt?
enable Boolean @default(false)
kitrows KitRow[]
adminroles KitAdminRole[]
requiredroles KitRequiredRole[]

@@unique([kitname, guildid])
}

model Role {
roleid BigInt
guildid BigInt
guild Guild @relation(fields: [guildid], references: [guildid])
kitadminroles KitAdminRole[]
kitrequiredroles KitRequiredRole[]

@@id([roleid, guildid])
}

model KitAdminRole {
kitid Int
roleid BigInt
guildid BigInt
kit Kit @relation(fields: [kitid], references: [kitid])
role Role @relation(fields: [roleid, guildid], references: [roleid, guildid])

@@id([kitid, roleid, guildid])
}

model KitRequiredRole {
kitid Int
roleid BigInt
guildid BigInt
kit Kit @relation(fields: [kitid], references: [kitid])
role Role @relation(fields: [roleid, guildid], references: [roleid, guildid])

@@id([kitid, roleid, guildid])
}


model KitRow {
kitrowid Int @id @default(autoincrement())
kitrowname String
kitrowactions KitActionRow[]
kitid Int
kit Kit @relation(fields: [kitid], references: [kitid])
kitrowemoji String

@@unique([kitrowname, kitid])
}

model KitActionRow {
kitrowactionid Int @id @default(autoincrement())
actiontypeid Int
actiontype ActionType @relation(fields: [actiontypeid],references: [actiontypeid])
kitrowid Int
kitrow KitRow @relation(fields: [kitrowid], references: [kitrowid])
roleid BigInt?
duration Int?
}

model ActionType {
actiontypeid Int @id
actiontypelibelle String
kitrowactions KitActionRow[]
}
// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

// Looking for ways to speed up your queries, or scale easily with your serverless or edge functions?
// Try Prisma Accelerate: https://pris.ly/cli/accelerate-init

generator client {
provider = "prisma-client-js"
}

datasource db {
provider = "sqlite"
url = "file:dev.db"
}

model Guild {
guildid BigInt @id
joinedAt DateTime @default(now())
prenium Boolean @default(false)
kits Kit[]
roles Role[]
}

model Kit {
kitid Int @id @default(autoincrement())
kitname String
guildid BigInt
guild Guild @relation(fields: [guildid], references: [guildid])
channelid BigInt?
enable Boolean @default(false)
kitrows KitRow[]
adminroles KitAdminRole[]
requiredroles KitRequiredRole[]

@@unique([kitname, guildid])
}

model Role {
roleid BigInt
guildid BigInt
guild Guild @relation(fields: [guildid], references: [guildid])
kitadminroles KitAdminRole[]
kitrequiredroles KitRequiredRole[]

@@id([roleid, guildid])
}

model KitAdminRole {
kitid Int
roleid BigInt
guildid BigInt
kit Kit @relation(fields: [kitid], references: [kitid])
role Role @relation(fields: [roleid, guildid], references: [roleid, guildid])

@@id([kitid, roleid, guildid])
}

model KitRequiredRole {
kitid Int
roleid BigInt
guildid BigInt
kit Kit @relation(fields: [kitid], references: [kitid])
role Role @relation(fields: [roleid, guildid], references: [roleid, guildid])

@@id([kitid, roleid, guildid])
}


model KitRow {
kitrowid Int @id @default(autoincrement())
kitrowname String
kitrowactions KitActionRow[]
kitid Int
kit Kit @relation(fields: [kitid], references: [kitid])
kitrowemoji String

@@unique([kitrowname, kitid])
}

model KitActionRow {
kitrowactionid Int @id @default(autoincrement())
actiontypeid Int
actiontype ActionType @relation(fields: [actiontypeid],references: [actiontypeid])
kitrowid Int
kitrow KitRow @relation(fields: [kitrowid], references: [kitrowid])
roleid BigInt?
duration Int?
}

model ActionType {
actiontypeid Int @id
actiontypelibelle String
kitrowactions KitActionRow[]
}
New file with no errors