I have three tables in my Postgres database - Titles, Attributes, and Titles_Attributes_Join - for a many-to-many lookup. I need to create an interface in AppSmith that allows users to select a Title and an Attribute which should update the Titles_Attributes_Join table. While using UPSERT works to add and update records, I'm struggling with how to handle deleting a record (in the Join table) when an attribute is unchecked. The current solution involves deleting the row using a query and then inserting new rows for the updated selection.
The solution is to use two select widgets in AppSmith that load the data from the Titles and Attributes tables. After selecting a value in each of these widgets, click a Button widget and run an upsert function in its onClick event that adds or updates the record in the Titles_Attributes_Join table. For example, an Admin Assistant (title_id = 1) would have access to Projects Folder (attribute_id = 1), Accounting Folder (attribute_id = 2), and HR Folder (attribute_id = 3). The Join table would store these relationships as records (1, 1), (1, 2), (1, 3). If Accounting Folder is unchecked, it should delete the (1,2) row in the join table. The solution is to create a query that removes based on what was unchecked and inserts the remaining records into the join table using an upsert function that handles adding and updating. The code example for the upsert function is as follows:
DELETE FROM titles_attributes_join
WHERE title_id = {{titles_data_table.selectedRow.id}}
INSERT INTO titles_attributes_join (title_id, title_attribute_id)
VALUES
{{ MultiSelect1.selectedOptionValues.map((item) => {
const key = titles_data_table.selectedRow.id;
return "('" + key + "'," + "'" + item + "')"
}).join(",") }}
ON CONFLICT ON CONSTRAINT uq_title_attribute_ids
DO NOTHING;