Sails.js Waterline UPDATE: How to handle multiple updates

Question

I have an array of objects that I want to update, thus a multiple update. I am looking for the best way to implement this. Each object includes the same properties, but the properties for each object have different values.

Do I implement something like this?

var people = [
    {'firstName': 'Brian', 'clockedIn': '2016-4-12', 'type': 'developer'},
    {'firstName': 'Taylor', 'clockedIn': '2016-4-14', 'type': 'developer'},
    {'firstName': 'Jake', 'clockedIn': '2016-4-14', 'type': 'manager'}
];

PersonModel.update({'type': 'developer'}, people, function(err, records) {
    // ...
    // ...
});

If I do something like the previous code, what exactly does it do? Does it automatically try to match the primary key of each record in the people array, update any of the properties that are listed, and then pass the records that were updated into the callback function?

I noticed in the Sails.js documentation here that the second argument for the update function can be an object OR an array, I am just unclear if I can use it like this. The documentation is unclear.

If I cannot use it like this and I wanted to try an iterative or recursive approach, how would you suggest I implement it?

i think you should try : var people = [ {'firstName': 'Brian'}, {'firstName': 'Taylor'}, {'firstName': 'Jake'} ]; — Ahmed farag mostafa, Apr 26 '16 at 21:38

score 1 · Accepted Answer · edited May 23 '17 at 11:53

What is your adapter ?

I had to do this with MySQL, I make the query my self, and then call PersonModel.query(myQuery)

I follow this answer to make it (example with two field) :

values = [
     {
         id: 1,
         param_1: 'NewValueParam1',
         param_2: 'NewValueParam2'
     },
     {
         id: 2,
         param_1: 'AnotherNewValueParam1',
         param_2: 'AnotherNewValueParam2'
     }
];

function multiValuesUpdate(values) {
    var request = "UPDATE MyTable SET ";
    var part_param_1 = "`param_1` = (CASE `id` ";
    var part_param_2 = "`param_2` = (CASE `id` ";
    var part_ids = "WHERE `id` IN (";

    _.forEach(values, function (v) {
        part_param_1 += "WHEN '" + v.id + "' THEN '" + v.param_1 + "' ";
        part_param_2 += "WHEN '" + v.id + "' THEN '" + v.param_2 + "' ";
        part_ids += v.id + ",";
    });
    part_param_1 += "ELSE `param_1` END), "; // Be careful with the comma !
    part_param_2 += "ELSE `param_2` END) ";
    part_ids = part_ids.slice(0, -1) + ");"; // Remove the last "," and add ");"

    request += part_param_1 + part_param_2 + part_ids;
    return request;
}

console.log(multiValuesUpdate(values));

Output :

"UPDATE MyTable 
   SET `param_1` = (
   CASE `id` 
      WHEN '1' THEN 'NewValueParam1' 
      WHEN '2' THEN 'AnotherNewValueParam1' 
      ELSE `param_1` 
   END), 
   `param_2` = (
   CASE `id` 
      WHEN '1' THEN 'NewValueParam2' 
      WHEN '2' THEN 'AnotherNewValueParam2' 
      ELSE `param_2` 
   END) 
 WHERE `id` IN (1,2);"

mysql adapter. So you loop through your array of objects and update your query string on each iteration? Then you just execute a query? It looks like we may have found a solution. We created a recursive function that seems to do what we need it to do. However, please expound on your answer if you want me to consider it. — Brian, Apr 27 '16 at 14:52
The recursive call is not a good idea, it can't make more than 256 iteration... I used [this](http://stackoverflow.com/questions/20255138/sql-update-multiple-records-in-one-query) to make the request. — M-A. Fernandes, Apr 27 '16 at 14:59
After looking into this, I think its a pretty good way to implement it. Thank you! The only thing I would suggest is to make the answer more clear and expound for anyone that stumbles upon this. — Brian, Apr 28 '16 at 21:02
@Brian Thanks, I just edit the answer with an example. I hope it's a little bit more clear — M-A. Fernandes, Apr 29 '16 at 06:54

Sails.js Waterline UPDATE: How to handle multiple updates

1 Answers1