Nick Posts - Answer Overflow

Nick

Explore posts from servers

•Created by Nick on 7/19/2024 in #help-and-questions

@relation field referencing a compound id

Is there a way to have an @relation that points to a compound id on another table? Consider this scenario: https://www.prisma.io/docs/orm/prisma-client/special-fields-and-types/working-with-composite-ids-and-constraints

model User {
  id    Int    @id @default(autoincrement())
  name  String
  post  Post[]
  likes Like[]
}

model Post {
  id      Int    @id @default(autoincrement())
  content String
  User    User?  @relation(fields: [userId], references: [id])
  userId  Int?
  likes   Like[]
}

model Like {
  postId Int
  userId Int
  User   User @relation(fields: [userId], references: [id])
  Post   Post @relation(fields: [postId], references: [id])

  @@id([postId, userId])
}

model User {
  id    Int    @id @default(autoincrement())
  name  String
  post  Post[]
  likes Like[]
}

model Post {
  id      Int    @id @default(autoincrement())
  content String
  User    User?  @relation(fields: [userId], references: [id])
  userId  Int?
  likes   Like[]
}

model Like {
  postId Int
  userId Int
  User   User @relation(fields: [userId], references: [id])
  Post   Post @relation(fields: [postId], references: [id])

  @@id([postId, userId])
}

Say we wanted to add a 4th table that looks like this:

model Review {
  id        String       @id @default(uuid())
  status    ReviewStatus @default(PENDING)

  // Reference to user table
  userId    String?
  reviewedBy User?          @relation(fields: [userId], references: [id])

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  // Reference to post table
  postId Int? @unique
  post   Post? @relation(fields: [postId], references: [id], onDelete: Cascade)
  
  // Reference to like table
  likeId Int? @unique
  like   Like? @relation(fields: [likeId], references: [postId, userId], onDelete: Cascade)
}

model Review {
  id        String       @id @default(uuid())
  status    ReviewStatus @default(PENDING)

  // Reference to user table
  userId    String?
  reviewedBy User?          @relation(fields: [userId], references: [id])

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  // Reference to post table
  postId Int? @unique
  post   Post? @relation(fields: [postId], references: [id], onDelete: Cascade)
  
  // Reference to like table
  likeId Int? @unique
  like   Like? @relation(fields: [likeId], references: [postId, userId], onDelete: Cascade)
}

I cannot figure out how to set up like Like? @relation(fields: [likeId], references: [postId, userId], onDelete: Cascade) since the primary key on the Like table is an implicit compound id. I cannot map two foreign columns to one column either. Any ideas?

1 replies

PPrisma

•Created by Nick on 3/25/2024 in #help-and-questions

Frequent constraint failed on id when using @id @default(autoincrement())

Is there anything I should know about how @id @default(autoincrement()) works? We're using postgresql. We find that if you delete a row and then insert that same row with the same id, we hit a prisma:error Unique constraint failed on the fields: (id). Additionally, we find that if you use a transaction to insert multiple rows at once, we also hit prisma:error Unique constraint failed on the fields: (id). This is a frequent issue, so much so we have fallbacks that compute an id number, which removes the whole point of auto-increment.

5 replies

Gaming

Programming