EAV prisma schema

[ledniy]

Is there a way to describe Entity-Attribute-Value relation in prisma?
An example from magento:

So i would like to fetch product with all attributes which can be from different tables due to their value types

[nikolasburk]

Hey @ledniy, thanks a lot for your question! I'm not too familiar with EAV but I tried to model your diagram using the Prisma schema:

datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

generator client {
  provider = "prisma-client-js"
}

model catalog_product_entity {
  entity_id      Int @default(autoincrement()) @id
  entity_type_id Int @unique
}

model eav_attribute {
  attribute_id   Int                    @default(autoincrement()) @id
  entity_type_id Int
  entity_type    catalog_product_entity @relation(fields: [entity_type_id], references: [entity_type_id])
  attribute_code String
}

model catalog_product_entity_attribute_varchar {
  attribute_id Int           @id
  attribute    eav_attribute @relation(fields: [attribute_id], references: [attribute_id])
  value        String
}

After generating Prisma Client, you'd now be able to query the tables that are described in the schema:

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

// A `main` function so that we can use async/await
async function main() {
 const products = await prisma.catalog_product_entity.findMany({
   where: {
     entity_type_id: {
       // ...
     }
   }
 })
}

main()

Please let me know if that helps and if you have any further questions!

[ledniy]

@nikolasburk not really i meant something like:

model product {
  id Int @default(autoincrement()) @id
  attributes[] @relation // should be list of all possible attributes with values
}

model attribute {
  id Int @default(autoincrement()) @id
  entity_id Int
  name String // Color, Size, etc
  type String // varchar, int, etc
}

model attribute_value_varchar {
  id Int @default(autoincrement()) @id
  product_id Int
  attribute_id Int
  value String
}

model attribute_value_int {
  id Int @default(autoincrement()) @id
  product_id Int
  attribute_id Int
  value Int
}

but i don't understand how to connect them with relations

[ledniy]

i would say the main requirement is to have product with attributes which can be different types of values like size, color, type and of course ability to filter by them

[nikolasburk]

If I understand correctly, you're asking for some type of inheritance or union types in the Prisma schema, is that correct? This is currently not supported unfortunately, but it would be great if you could add the EAV use case to the respective GitHub issue here: #253

Also, if you would be great to know what your desired schema actually looks like in SQL so that we can think about how this should map to the Prisma schema 🙂

[ledniy]

@nikolasburk Thanks, yeah i guess something like union. I'll add detailed example to the #253

[nikolasburk]

Perfect, thanks a lot! 🙏 If there are not further questions, it would also be great if you could mark this questions as answered 🙂