This query is to much for me :)
Hello guys,
This is the Query:
One of the response items:
This is what I'm looking for, and I'm thinking that I will have to do it manually (like iterating the result):
Instead of all custom fields in that field, make each one of them an individual field, like below.
I would love to know your opinion
const result = await db
.select({
contact_id: contacts.id,
contact_name: contacts.name,
custom_fields_data: sql`group_CONCAT(${customFields.field_key}, ': ', ${contactCustomFields.value})`,
})
.from(contactCustomFields)
.innerJoin(contacts, eq(contacts.id, contactCustomFields.contact_id))
.innerJoin(customFields, eq(customFields.id, contactCustomFields.custom_field_id))
.groupBy(contacts.id, contacts.name)
.execute()
const result = await db
.select({
contact_id: contacts.id,
contact_name: contacts.name,
custom_fields_data: sql`group_CONCAT(${customFields.field_key}, ': ', ${contactCustomFields.value})`,
})
.from(contactCustomFields)
.innerJoin(contacts, eq(contacts.id, contactCustomFields.contact_id))
.innerJoin(customFields, eq(customFields.id, contactCustomFields.custom_field_id))
.groupBy(contacts.id, contacts.name)
.execute()
json
{
"contact_id": 263071998,
"contact_name": "Sanchez - Tennessee - 3",
"custom_fields_data": "data_transfer__do_not_use: 7/27/2020,total___of_people_in_family: 3,how_old_is_the_youngest_member_of_the_family: 8+,nationality: Honduras,sponsor: Julia Gutierrez,ice_check_in_date: 8/7/2019,ice_check_in_location: Nashville, TN,record_type: Emergency Support"
},
json
{
"contact_id": 263071998,
"contact_name": "Sanchez - Tennessee - 3",
"custom_fields_data": "data_transfer__do_not_use: 7/27/2020,total___of_people_in_family: 3,how_old_is_the_youngest_member_of_the_family: 8+,nationality: Honduras,sponsor: Julia Gutierrez,ice_check_in_date: 8/7/2019,ice_check_in_location: Nashville, TN,record_type: Emergency Support"
},
json
{
"contact_id": 263071998,
"contact_name": "Sanchez - Tennessee - 3",
"address_country": "United States",
"address_city": "Nashville",
"address_line1": null,
"address_postal_code": "37211",
"address_state": "Tennessee",
"description": null,
"email": null,
"customer_status": "none",
"data_transfer__do_not_use: "7/27/2020",
"total___of_people_in_family": 3,
"how_old_is_the_youngest_member_of_the_family": "8+",
"nationality": "Honduras"
...
}
json
{
"contact_id": 263071998,
"contact_name": "Sanchez - Tennessee - 3",
"address_country": "United States",
"address_city": "Nashville",
"address_line1": null,
"address_postal_code": "37211",
"address_state": "Tennessee",
"description": null,
"email": null,
"customer_status": "none",
"data_transfer__do_not_use: "7/27/2020",
"total___of_people_in_family": 3,
"how_old_is_the_youngest_member_of_the_family": "8+",
"nationality": "Honduras"
...
}
1 Reply