P
Prisma•5d ago
Blueluelueluelue

Join a column with multiple tables.

I want to store all media in the system in a central table. Now the associated resource of the media can be anything, like chat, project, ticket, etc. So I have a Media model like this 
model Media {
  id                 String    @id 
  resource_id        String
  resource_type      String  
  resourceA          ResourceA?  @relation("ResourceA", fields: [resource_id], references: [id])  
  resourceB          ResourceB?  @relation("ResourceA", fields: [resource_id], references: [id])
}
model Media {
  id                 String    @id 
  resource_id        String
  resource_type      String  
  resourceA          ResourceA?  @relation("ResourceA", fields: [resource_id], references: [id])  
  resourceB          ResourceB?  @relation("ResourceA", fields: [resource_id], references: [id])
}
and an arbitrary number of resource models 
model ResourceA {
    id String @id
}
model ResourceB {
    id String @id
}
model ResourceA {
    id String @id
}
model ResourceB {
    id String @id
}
It compiles just fine but during insertion it fails because a foreign key constraint get violated. When I try to insert a media associated with ResourceA, the ResouceB foreign key constraint gets violated. I know a simple solution would be have a column for each resource table like resourceAId, resourceBId and so on but is there a more elegant way?
1 Reply
Nurul
Nurul•4d ago
Hey 👋 It looks like you want to implement Polymorphism, which is currently not natively supported.: https://github.com/prisma/prisma/issues/1644 You could use separate columns for each resource type (resourceAId, resourceBId, etc.). This is not ideal but would work as you suggested. You could use a single relation field with a type enum as well, something like: 
model Media {
  id            String       @id
  resource_id   String
  resource_type ResourceType
  resourceA     ResourceA?   @relation(fields: [resource_id], references: [id])
  resourceB     ResourceB?   @relation(fields: [resource_id], references: [id])
}

model ResourceA {
  id    String  @id
  media Media[]
}

model ResourceB {
  id    String  @id
  media Media[]
}

enum ResourceType {
  A
  B
}
model Media {
  id            String       @id
  resource_id   String
  resource_type ResourceType
  resourceA     ResourceA?   @relation(fields: [resource_id], references: [id])
  resourceB     ResourceB?   @relation(fields: [resource_id], references: [id])
}

model ResourceA {
  id    String  @id
  media Media[]
}

model ResourceB {
  id    String  @id
  media Media[]
}

enum ResourceType {
  A
  B
}
GitHub
Support for Polymorphic Associations · Issue #1644 · prisma/prisma
I have the following schema: model Post { id String @default(cuid()) @id } model Comment { id String @default(cuid()) @id } model Vote { id String @default(cuid()) @id item Post | Comment } I'd...

Did you find this page helpful?