Best practices for populating many-to-many tables

Queries and Resources
,
1

I'm connected to a MySQL database and one of the tables is a many-to-many intermediate table. I'd like to build a module (component?) that will allow me to make several updates to this intermediate table.

So far, I've come up with the idea of populating two separate checkbox groups from my two primary tables and placing those in the form. Ideally, choosing items from group 1 and group 2 would INSERT IGNORE several rows in the intermediate table.

CleanShot 2022-09-06 at 13.43.06@2x
CleanShot 2022-09-06 at 13.43.06@2x1802×524 24.2 KB

Using the example above, clicking submit would execute the equivalent of:

INSERT IGNORE INTO person_favorite (name, fav)
VALUES ('mary','rolo');

INSERT IGNORE INTO person_favorite (name, fav)
VALUES ('mary','snickers');

INSERT IGNORE INTO person_favorite (name, fav)
VALUES ('jane','rolo');

INSERT IGNORE INTO person_favorite (name, fav)
VALUES ('jane','snickers');

INSERT IGNORE INTO person_favorite (name, fav)
VALUES ('fred','rolo');

INSERT IGNORE INTO person_favorite (name, fav)
VALUES ('fred','snickers');

alternatively...

INSERT IGNORE INTO person_favorite (name, fav) 
VALUES ('mary','rolo'),('mary','snickers'),('jane','rolo'),('jane','snickers'),('fred','rolo'),('fred','snickers');

Admittedly, I'm new to retool, but I can't figure out a way to convert the array results from the checkboxGroups to a working version of the SQL statement above.

update (09/06/2022 @ 2041): considering a javascript transformer. thoughts?

Thanks in advance for the help.

2

Wanted to provide an update for posterity. I ended up [mostly] solving this with a transformer and will use a bulk insert method. I'm certain there's a more elegant way (i.e. .map() .reduce(), but this seems to do the trick for now.

var people = {{tablePerson.value}}
var favorites = {{checkboxFavorites.value}}

const people_pairs = [];
favories.forEach(favorite => {
  people.forEach(person => {
    const obj = {person_id:person, favorite_id:favorite};
    people_pairs.push(obj);
  });
});
return people_pairs;
3