Hi,
I am new to retool and try to build my first app here. My data is inside a Azure CosmosDB (MongoDB API).
That is the structure of the stored json documents:
{
"_id": {
"$oid": "XXXXXXXX"
},
"relatedOe": {
"nameOE1": {
"name": "company1",
"sevDeskContactId": "XXXXX",
"userCount": 23,
"lastUpdate": "2023-10-31 07:20:13",
"webinarFlatrate": false,
"createInvoice": true,
"dateOfActivePayment": "2023-05-30 00:00:00"
},
"nameOE2": {
"name": "company2",
"sevDeskContactId": "XXXXXXX",
"userCount": 54,
"lastUpdate": "2023-10-31 07:20:12",
"webinarFlatrate": false,
"createInvoice": true,
"dateOfActivePayment": "2023-05-30 00:00:00"
},
"nameOE3": {
"name": "company3",
"sevDeskContactId": "XXXXXX",
"userCount": 23,
"lastUpdate": "2023-10-31 07:20:12",
"webinarFlatrate": false,
"createInvoice": true,
"dateOfActivePayment": "2023-10-01 00:00:00",
"useSEPA": true,
"sendInvoice": true,
"setupIntent": "123123123123123123123123"
}
},
"name": "Big Holding",
"aggregatedInvoice": false,
"dateOfActivePayment": "2023-05-30 00:00:00",
"createInvoice": true,
"priceModel": "iouaosjdlksjdlkjajsd",
"priceKey": "12314324234",
"webinarPriceKey": "123123123",
"aggregatedUserCount": 100,
"id": 79,
"dateCreated": "2023-07-16 18:23:18"
}
I visualize the data in a normal (new) table (objects in relatedOE are not shown in the table) and some columns there are editable. So I was looking for a way to store updates that have been done to the editable columns (relatedOE is not part of it as this is handled in a detailed view.
So the best way That I could find (using Chat GPT) is following:
- JavaScript to iterade ofer changes in "table1.changesetArray" and calls a MongoDB query to update the documents
- Resource query MongoDB to update every single object
Here is the code...
JavaScript snippet:
let changesetArray = table1.changesetArray;
changesetArray.forEach(change => {
let updateFields = {};
for (let key in change) {
if (change.hasOwnProperty(key) && key !== '_id') {
updateFields[key] = change[key];
}
}
// Trigger the update query
updateCompany.trigger({
additionalScope: {
idToUpdate: change._id,
updateFields: updateFields
}
});
});
table1.refresh()
Mongo DB Query:
I tested it and it is working BUT as the MongoDB query is sometime called several times and there is some lag of the Database, it actually feels like not a good solution.
I would be very happy if more expirienced community members here tell me about the best way to handle changes for a mongoDB in retool? Unfrotunately I could not find any Best Practices online.