how to make a relationship with a table column to multiple table based on condition in prisma ORM

Question

I have a model

model image {
  id                Int       @id @default(autoincrement())
  entityId          Int
  entityTypeId      Int
  imageUrl          String
  recordStatusRefId Int?
  createdBy         Int?
  createdOn         DateTime  @default(now())
  updatedBy         Int?
  updatedOn         DateTime?
  isDeleted         Int       @default(0)
}

I need a relationship for entityId to village table when entityTypeId = 1 and map entityId to property table when entityTypeId = 2

is this prossible in PRISMA ORM

I tried to define two relations but it getting error

Invalid relationship like that. Is it possible to define a relationship like this? — Aravind Krishnan, Apr 21 '23 at 04:51
error: Error parsing attribute "@relation": The given constraint name `tbl_image_entityId_fkey` has to be unique in the following namespace: global for foreign keys. Please provide a different name using the `map` argument. — Aravind Krishnan, Apr 21 '23 at 05:05

score 0 · Answer 1 · answered Apr 21 '23 at 05:53

Mapping relations according to a value of another field is currently not possible in Prisma and in my opinion out of the scope of an ORM unless it's a NoSQL database.

As a workaround, you can handle this in the application layer instead of the database layer. Two solutions come to my mind:

Recommended way: Create 2 separate optional relation fields (for e.g. villageEntityId and propertyEntityId). This way, you have to manually handle the creation and update logic but you can easily populate your queries when calling the image model.
NOT recommended: Create the entityId field without specifying relations and manually call a 2nd query to the village or property table according to your entityTypeId value to get the required data. This way, you are basically managing the relations in the application layer and therefore it is not great if you have to populate data in a large collection.

how to make a relationship with a table column to multiple table based on condition in prisma ORM

1 Answers1