How to create one to many relationship from many to many join table in Prisma

Question

I am creating a workout app and would like to model the relationship between users and workout programs. A user can create a program multiple times.

Here are my Prisma models:

model User {
  id                      Int         @id @default(autoincrement())
  createdAt               DateTime    @default(now())
  email                   String      @unique
  firstName               String      @db.VarChar(50)
  lastName                String      @db.VarChar(50)
  password                String      @db.VarChar(191)
  
  programs                ProgramEnrollment[]
  
}

model ProgramEnrollment {
  program                 Program     @relation(fields: [programId], references: [id])
  programId               Int // relation scalar field (used in the `@relation` attribute above)
  user                    User @relation(fields: [userId], references: [id])
  userId                  Int // relation scalar field (used in the `@relation` attribute            
  assignedAt              DateTime @default(now())

  @@id([programId, userId])
}

model Program {
  id                    Int                 @id @default(autoincrement())
  name                  String   
  users                 ProgramEnrollment[]
}

The above works nicely, but now what I am trying to do is let the user record their personal program results, so I add the following:

model ProgramEnrollment {
  program                 Program     @relation(fields: [programId], references: [id])
  programId               Int // relation scalar field (used in the `@relation` attribute above)
  user                    User @relation(fields: [userId], references: [id])
  userId                  Int // relation scalar field (used in the `@relation` attribute            
  assignedAt              DateTime @default(now())
  userProgram             UserProgram[] 

  @@id([programId, userId])
}

model UserProgram {
  id                        Int                 @id @default(autoincrement())
  name                      String

  userProgramEnrollment     ProgramEnrollment @relation(fields: [programEnrollmentId], references: [id])
  programEnrollmentId       Int // relation scalar field (used in the `@relation` attribute above)
} 

When I make the above changes I get the following error: Error validating: The argument references must refer only to existing fields in the related model ProgramEnrollment. The following fields do not exist in the related model: id

Why will it not let me create a one to many relationship from a many to many join table?

Answer 1

I just need to adjust the UserProgram model a bit to account for multi-field id in the ProgramEnrollment model.

model ProgramEnrollment {
  program                 Program     @relation(fields: [programId], references: [id])
  programId               Int // relation scalar field (used in the `@relation` attribute above)
  user                    User        @relation(fields: [userId], references: [id])
  userId                  Int // relation scalar field (used in the `@relation` attribute            
  assignedAt              DateTime    @default(now())
  userProgram             UserProgram[] 

  @@id([programId, userId])
}

model UserProgram {
  id                           Int                 @id @default(autoincrement())
  name                         String
  userProgramEnrollment        ProgramEnrollment @relation(fields: [programEnrollment_programId,  programEnrollment_userId], references: [programId, userId])
  programEnrollment_programId  Int
  programEnrollment_userId     Int 
} 

Since ProgramEnrollment uses two fields for its id, we have to reference both of them in the UserProgram model.

Answer 2

As docs states composite ID (@@id) cannot be defined on a relation field.

You can probably use @@unique to define a compound unique constraint instead, like that: @unique([programId, userId]), and then just use regular autogenerated id for ProgramEnrollment and then you will be able to use it in a relation for UserProgram