INSERT data into Join/Junction Table

Question and Answers Data Source
1

I have three tables in a Postgres database - Titles, Attributes, and Titles_Attributes_Join. The Titles table contains a list of titles and the Attributes table contains a list of attributes. The Titles_Attributes_Join table is a JOIN/JUNCTION table that stores the primary keys for titles and attributes so I can have a MANY TO MANY lookup. My question is how in AppSmith would I build an interface so when you select a Title and an Attribute it would update the Titles_Attributes_Join table?

2

Hi Jeff and welcome to our community! I think the solution would be to use 2 select widgets that load the data from Titles and Attributes each, then after selecting a value in each of these widgets, you click a Button widget and run an update query in its onClick event that updates the Titles_Attributes_Join table.
Please also take a look at our sample apps and fork them to check the code if you find any of them helpful for your use case.

3

I went down that path and used an UPSERT function to handle adding and updating. The problem I have now is how to handle when one gets unchecked and needs to get DELETED.

4

You want to delete both the record in the table and the Joined table (for many to many) as well?

5

No.
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 haves 3 records - (1, 1), (1, 2), (1,3). And in the Multitree this works great to display. But if I remove say Accounting Folder it should delete the (1,2) row in the join table.

6

In this case, you could create a query that’ll remove based on what was unchecked. Since they all have a common attribute (Admin Assistant), I assume there should be a way to know before hand.

7

I kind of feel like this is cheating but it’s working.

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;
8

It’s definitely cheating :sweat_smile: but it’s quite okay.
I can’t think of a better way right now