Prisma•4mo ago

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✧•4mo 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•4mo 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•4mo 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✧•4mo 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

Gaming

Programming

Implicite Many-to-Many relationship double primary key