john093e
Explore posts from serversDTDrizzle Team
•Created by john093e on 12/31/2023 in #help
How to fetch and flatten an EAV model ?
Hello dear Drizzle community 🙂
I got a Saas multi-tenant project.
Tenants ( called "Organizations" ) have their own Contacts and are capable to add as many Properties as they want and so add Values to does properties for each contact. Objectif is to be as much flexible as possible.
I have build my database scheme following principles of an EAV model (entity-attributes-values) .
Entity - > Contacts table
Attributes -> Properties table
Values -> Contacts Properties Values table.
I have attached examples of the schemes.
Note : json seems good but as the number of properties will grow i though it would be a mess after to much data (please if i'm wrong stop me here 🙂 )
I need help on the following :
When fetching contact data, I got the organizationId and an array of the desired properties to fetch containing property id and property name.
With the "query" feature using 'findMany' and apply my filters / conditions to fetch users and their properties. I got such return :
[{
"contactId": "1XXXXXXXX",
"contactsPropertiesValues": [{
"id": "AZERTYXXXX",
"valueString": "[email protected]",
"valueText": null,
"valueDate": null,
"valueBoolean": null,
},
{
"id": "POIUYXXX",
"valueString": "John",
"valueText": null,
"valueDate": null,
"valueBoolean": null,
}],
},
{
"contactId": "2XXXXXXXX",
"contactsPropertiesValues": [{
"id": "QWERTYXXXX",
"valueString": "[email protected]",
"valueText": null,
"valueDate": null,
"valueBoolean": null,
}],
}]
The shape of the result isn't what i search for... Using "select" seems to be the right way for me but I can't get what i want neither.
My ultimate goal in this selection is to succeed to return a flatten response like :
[{
"contactId" : "1XXXXXXXXXX",
"email": "[email protected]",
"firstname": "doe",
},
{
"contactId": "2XXXXXXXXX",
"email": "[email protected]",
"firstname": null,
}]
27 replies