john093e
Explore posts from serversDTDrizzle Team
•Created by john093e on 12/31/2023 in #help
How to fetch and flatten an EAV model ?
it would be Amazing !
27 replies
DTDrizzle Team
•Created by john093e on 12/31/2023 in #help
How to fetch and flatten an EAV model ?
27 replies
DTDrizzle Team
•Created by john093e on 12/31/2023 in #help
How to fetch and flatten an EAV model ?
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
27 replies
DTDrizzle Team
•Created by john093e on 12/31/2023 in #help
How to fetch and flatten an EAV model ?
27 replies
DTDrizzle Team
•Created by john093e on 12/31/2023 in #help
How to fetch and flatten an EAV model ?
re-uploading file for privacy reasons 🙂
27 replies
DTDrizzle Team
•Created by john093e on 12/31/2023 in #help
How to fetch and flatten an EAV model ?
Many thanks already @Angelelz for taking the time to understand my context and for any help you can provide 🙂
27 replies
DTDrizzle Team
•Created by john093e on 12/31/2023 in #help
How to fetch and flatten an EAV model ?
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,
}]
27 replies
DTDrizzle Team
•Created by john093e on 12/31/2023 in #help
How to fetch and flatten an EAV model ?
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 )
27 replies
DTDrizzle Team
•Created by john093e on 12/31/2023 in #help
How to fetch and flatten an EAV model ?
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 ...
27 replies
DTDrizzle Team
•Created by john093e on 12/31/2023 in #help
How to fetch and flatten an EAV model ?
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
27 replies
DTDrizzle Team
•Created by john093e on 12/31/2023 in #help
How to fetch and flatten an EAV model ?
how can i pivot / flatten my selection ?
is it even possible with drizzle ?
in advance thanks to everyone helping me ! 🙂
27 replies