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,
}]
10 Replies
how can i pivot / flatten my selection ?
is it even possible with drizzle ?
in advance thanks to everyone helping me ! 🙂
Hello again 🙂
Do I need to add more details about my issue / help request ? 🙂
Is it possible to do ?
is there a better way ?
can anyone help me please 🙂
in advance thanks
Sure, can you show the select you attempted to use?
in the process to return contacts with their properties like this :
[{
"contactId" : "1XXXXXXXXXX",
"email": "[email protected]",
"firstname": "doe",
},
{
"contactId": "2XXXXXXXXX",
"email": "[email protected]",
"firstname": null,
}]
I first fetch the requestd columns (properties) data ( id, internal name, type)
Here is my selection of columns(properties) :
// Get the selected view columns data
const selectedViewColumns = await ctx.db
.select({
id: schema.properties.id,
name: schema.properties.name,
internalName: schema.properties.internalName,
type: schema.properties.type,
})
.from(schema.b2eSelectedViews)
.where(
and(
eq(schema.b2eSelectedViews.b2eId, b2eId),
eq(schema.b2eSelectedViews.viewId, input.viewId),
eq(schema.b2eSelectedViews.object, "contacts")
)
)
.innerJoin(
schema.views,
eq(schema.b2eSelectedViews.viewId, schema.views.id)
)
.innerJoin(
schema.viewsColumns,
eq(schema.views.id, schema.viewsColumns.viewId)
)
.innerJoin(
schema.properties,
eq(schema.viewsColumns.propertyId, schema.properties.id)
)
.orderBy(asc(schema.viewsColumns.position))
this return :
selectedViewColumns = [
{
id: '01HJK5FDVXHAH1XJNTH6VHQ5BP',
name: 'Email',
internalName: 'email',
type: 'single-line text'
},
{
id: '01HJK5FF45M96ZN4C24YJHMQRG',
name: 'Phone Number',
internalName: 'phone',
type: 'phone number'
},
... I truncate the response because of text limit
]
based on this array a then attend to fetch my contacts ... to be continued next message ...
From the selected view I build an array containing my query of requested Properties
const requestedRows = []
for (const rowColumn of selectedViewColumns) {
const query = and(
eq(schema.contactsPropertiesValues.contactId, schema.contacts.id),
eq(schema.contactsPropertiesValues.propertyId, rowColumn.id)
)
requestedRows.push(query)
}
Then i attend to fetch contacts.
Here are some attend to fetch contacts :
1- With the condition in left joins : (modified answer, delete attached file, re uploaded two messag later )
First with this approach each time it fetch a property for a contact it return a new row so i got duplicate contacts
secondly I m fully lost about how i could return a flatten response like :
[{
"contactId" : "1XXXXXXXXXX",
"email": "[email protected]",
"firstname": "doe",
},
{
"contactId": "2XXXXXXXXX",
"email": "[email protected]",
"firstname": null,
}]
Many thanks already @Angelelz for taking the time to understand my context and for any help you can provide 🙂
re-uploading file for privacy reasons 🙂
Yeah, give me some time, I'll go through it and share my thoughts
So, all you need is contactId, email and first name from that query?
It looks like the email is in
valueString but I don't know where the name is pulled from
Correct me if I'm wrong but it looks like all you need is select the fields you need from that query:
Thanks for your proposition, sadly this won't work in my context of Entity Attribute Values database.
When fetching contact data everything is dynamic.
I first fetch the columns that users want to fetch when fetching contacts data inside the selectedViewColumns. So It can be 1 columns or an 100 of columns.
based on the selectedViewColumns i then fetch the contacts
"contacts" is my Entity table and got only 2 columns :
- id
- organizationId
All other columns are splitted into 2 other tables.
"properties" table contains the Attributes definition of the columns.
"contactsPropertiesValues" table contains the Values.
I found those articles on google that are sort of explaining my context :
https://www.googlecloudcommunity.com/gc/Technical-Tips-Tricks/SQL-processing-and-data-analysis-with-the-EAV-model/ta-p/589802
and this article the option 1 is close to what i got and need :
https://www.googlecloudcommunity.com/gc/Modeling/Three-ways-to-model-EAV-schemas-and-many-to-many-relationships/td-p/562454?postid=9627
SQL processing and data analysis with the EAV model
This content, written by Mike Xu, was initially posted in Looker Blog on Dec 9, 2014. The content is subject to limited support. Much of the world's data is stored in an . The EAV model is a commonly used in scientific research, medicine, healthcare, and popular open source and commercial software...
Three ways to model EAV schemas and many-to-many relationships
Introduction The entity-attribute-value (EAV) schema is often encountered in business tools where the number of possible attributes for an entity is very large, but any given record only has data for a handful of them. This schema is very flexible and allows for efficient storage of sparse data, but...
I think I'm starting to understand your setup. I'll need more time here, I'm at work right now
I have the feeling this can be done in one query
Many many thanks for your help ! 🙂
here is a simplified (but still complex) scheme of my tables and what i attend without success to return :
it would be Amazing !
There are a couple problems we'll need to solve in order to get the data with the shape you'd like
The biggest one is going to be the types, I'm not sure we'll be able to get correct types for this query
I see that the name of the columns is actually stored in the database in the
name/internalName of the properties table. Meaning the keys to the object you want will be dynamic and not know at compile time
I don't see a way to have correct or meaningful typescript types for this query
I didn't have time today to fully understand/solve the problem.
I think you should DM me because I believe this goes beyond regular drizzle help/support
I think the query can be constructed, probably employing a CTE and a huge case/when to be able to grab the correct type from the properties values table
Since each property can be a text, number, date, boolean or json