Can I map two variables values in SQL modes with Additional Scope? (Composite Primary Keys, Many to Many Tables)

[Carlos_Ortiz]

Hello! I have a many to many relationship table and I want to do a bulk update, but it's a little bit tricky so I'm a little bit lost. My Idea, first is to separate the other fields of the table into another bulk update so you can ignore the rest since those belong to another table and only focus on on the many to many part.

But is it possible to map an a object within the SQL mode such that you get a two variable value like for example, pass the changesetObject through a javascript query using additional scope variables in order to get an array of the values to update and map them?

{{ setOfValuesToUpdate.map(array => (firstNumber, secondNumber))}}

This is the idea that I have but I just cannot wrap my head around it, and at the end I just think is not possible and I think there might be better ways of doing this. I'll leave an image to see if it helps, what is that I'm trying to do.

Important Edit: I'm working with composite keys, so a normal bulk update or upsert won't work.

[Carlos_Ortiz]

This is a little bit what I'm picturing, to be a little bit more clear.

[ScottR]

You can do a Bulk Upsert

[Carlos_Ortiz]

Thanks for the reply!

Although I have to clarify, I don't think that will work here since I'm working with a table that uses composite primary keys. The bulk upsert won't know which primary to handle sinde there are two fields, and then there are duplicates of them and then where do you send which value to where?

[ScottR]

You could try the following:

const promises = icecream_table.changesetArray.map((h) => {
  return upsert_your_ids_query.trigger({
    additionalScope: {
      ice_cream_id: h.ice_cream_id,
      chef_id: h.chef_id
    },
  });
});
return Promise.all(promises);

upsert_your_ids_query

INSERT INTO ice_cream_chefs(ice_cream_id, chef_id) 
VALUES ({{ice_cream_id}}, {{chef_id}})
ON CONFLICT DO UPDATE SET 
ice_cream_id = excluded.ice_cream_id, 
chef_id = excluded.chef_id
WHERE your_schema.icecream_chefs.ice_cream_id = {{ice_cream_id}}

You can also add ON CONSTRAINT after ON CONFLICT if need be

[Carlos_Ortiz]

ScottR, thanks so much for the reply! And sorry for answering so late. Your answer helped me to start working on a solution, but it has taken me a little more than I thought.

With a couple of mishaps it seems that it could work but I have some concerns. As of right now how is working, let say I perform the following changes on my table:

Just describing the changes on the Chefs field: I wiped out all of the chefs on row 3 and I add 3 chefs on the first row.

So the queries are the following:

1.- One Bulk update for the the whole table with an _.omit for the chefs field, that includes the nuts, the flavours, the prices.
2.- Two queries for each row that has to be clear up, whether it was cleared up fully or it has to be map up again for a new line-up of chefs (This is because you don't know if the user remove a chef and not only added new ones)
3.- Three Queries for the three Chefs that where added on the first row individually.

My worry is that although it seems to work. Also, on the table that we are working with, the workload is not that heavy, but it doesn't seems to be an option that escalates very well, should I worry about it? That's why I was wondering if I could make complex mapping on the SQL mode so I could reduce the amount of queries I was making.

Also the logic of my javascript it's having some issue so I'm not so sure if I should share it here or if I should open a new question on the forums.