Using Prisma/Mysql in Polymorphic Association

321 Views Asked by At

Having several mysql tables (Toys, Users, Pets), where you want to add Categories in a many to many relation. Im wondering how the Prisma Schema should be, to include the Relations in it when using this Polymorphic Association so i can do better queries without having to join tables.

CREATE TABLE `Categories` (
  `id` VARCHAR(36) NOT NULL PRIMARY KEY,
  `name` VARCHAR(255)
);
CREATE TABLE `Toys` (
  `id` VARCHAR(36) NOT NULL PRIMARY KEY,
  `companionName` VARCHAR(255),
);
CREATE TABLE `Users` (
  `id` VARCHAR(36) NOT NULL PRIMARY KEY,
  `userName` VARCHAR(255),
);
CREATE TABLE `Pets` (
  `id` VARCHAR(36) NOT NULL PRIMARY KEY,
  `petName` VARCHAR(255),
);
CREATE TABLE `CategoryRelation` (
  `id` VARCHAR(36) NOT NULL PRIMARY KEY,
  `categoryId` VARCHAR(36),
  `entityId` VARCHAR(36),
  `entityType` VARCHAR(255), //Toys, Users, Pets
  `created_at` datetime
  FOREIGN KEY (`categoryId`) REFERENCES `Category`(`id`)
);

The Prisma Model i woud like to have is something similar to this. (added <-- ??? where i dont know what to use). Please take into account this is a basic example to show the problem, but we are working on a real and more complex database where we want to implement Prisma, so we cannot create lots of Many-to-Many automatic prisma relation tables, we need to continue with this Polymorphic approach

model Categories {
  id          Int     @id @default(autoincrement())
  name        String
  toys        Toys[]
  pets        Pets[]
  users       Users[]
}

model Toys {
  id            Int     @id @default(autoincrement())
  companionName String

  categories    Category[] @relation("ToyCategoryRelation") <-- ????
}

model Users {
  id            Int     @id @default(autoincrement())
  userName      String

  categories    Category[] @relation("UserCategoryRelation") <-- ????
}

model Pets {
  id            Int     @id @default(autoincrement())
  petName       String

  categories    Category[] @relation("PetCategoryRelation") <-- ????
}

model CategoryRelation {
  id          Int     @id @default(autoincrement())
  categoryId  Int
  entityId    Int          
  entityType  String  // Toys, Pets, Users
  created_at  DateTime

  category    Category @relation(fields: [categoryId], references: [id])
  entity      [Toys/Pets/User]??? @relation(fields: [entityId], references: [id])   <-- ????
}
0

There are 0 best solutions below