Nodejs multiple sql query loop

Question

I am pretty new to nodejs and async worlds.

The case is, I have an array like var ids = [1, 2, 3, 4];

I need to update mytable according to sequence of the array element. So I do something like:

sort: function(ids, callback) {

 // dont worry about this
  this.create_connection();
  this.connection.connect();

   for(var i=0; i<ids.length;i++) {
      var q = "UPDATE mytable SET sequence="+i+" where id="+ids[i]+"; ";

      this.connection.query(q, function(err, result) {

       // I am not sure about this
       // callback(err);
     });
   }

 // I need to return callback at the end
 // return callback();

  this.connection.end();

}

But yes.. it does not work because I have to return callback.. I think I need to do the query syncronously.. I am not sure. Please help thanks.

why you are not using forEach loop function

Atul Agrawal
– Atul Agrawal

2016-06-15 09:01:26 +00:00
Commented Jun 15, 2016 at 9:01 — Atul Agrawal
– Atul Agrawal, Commented Jun 15, 2016 at 9:01
it is synchronous version on for loop

Atul Agrawal
– Atul Agrawal

2016-06-15 09:01:44 +00:00
Commented Jun 15, 2016 at 9:01 — Atul Agrawal
– Atul Agrawal, Commented Jun 15, 2016 at 9:01

richardtz · Accepted Answer · 2016-06-15 09:52:47Z

2

If you are new to async worlds, you should take a look at module 'async'.

You can then do something like this :

async.forEachOfSeries(ids, function(id,index,callback){
     var q = "UPDATE mytable SET sequence="+index+" where id="+id+"; ";

      this.connection.query(q, function(err, result) {         
         callback();
     });


},function done(){
    // whatever you want to do onces all the individual updates have been executed.
})

edited Jun 15, 2016 at 9:52

answered Jun 15, 2016 at 8:56

richardtz

4,9932 gold badges29 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

egig Over a year ago

thanks.. where should I initialize and increment the i variable ??

richardtz Over a year ago

modified the example so you can have the index (use the forEachOf methods)

tex · Accepted Answer · 2016-06-15 12:29:02Z

0

See my inline comments:

sort: function(ids, callback) {
  this.create_connection();
  this.connection.connect();
  var q = "UPDATE mytable SET sequence CASE id ";

  // Don't execute one query per index in ids - that's inefficient
  // Instead, pack up all the queries and execute them at once
  for(var i=0; i<ids.length;i++) {
    q += "WHEN " + ids[i] + " THEN " + i + " ";
  }
  q += "ELSE sequence END;";

  // The sort method will return the result of connection.query
  return this.connection.query(q, function(err, result) {
    // End the connection
    this.connection.end();
    if(err) {
      // Handle any error here
      return callback(err);
    }
    // Otherwise, process, then return the result
    return callback(err, result);
  });
}

And here's something slightly more elegant:

sort: function(ids, callback) {
  this.create_connection();
  this.connection.connect();

  // Don't execute one query per index in ids - that's inefficient
  // Instead, pack up all the queries and execute them at once
  var q = ids.reduce(function(pv, cv, ci){
    return pv + " WHEN " + cv + " THEN " + ci + " ";
  }, "UPDATE mytable SET sequence CASE id ") + " ELSE sequence END;";

  // The sort method will return the result of connection.query
  return this.connection.query(q, function(err, result) {
    // End the connection
    this.connection.end();
    if(err) {
      // Handle any error here
      return callback(err);
    }
    // Otherwise, process, then return the result
    return callback(err, result);
  });
}

And you can replace the .reduce in the previous example with the following, if you want to use ES6 arrow functions:

var q = ids.reduce((pv, cv, ci) => pv + " WHEN " + cv + " THEN " + ci + " ",
          "UPDATE mytable SET sequence CASE id ") + " ELSE sequence END;";

edited Jun 15, 2016 at 12:29

answered Jun 15, 2016 at 8:37

tex

2,7661 gold badge22 silver badges31 bronze badges

2 Comments

egig Over a year ago

hi Tex thanks.. I get parse error

{ [Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE mytable  SET sequence=1 where id=1; UPDATE mytable  SET seq' at line 1]   code: 'ER_PARSE_ERROR',   errno: 1064,   sqlState: '42000',   index: 0 }

tex Over a year ago

You're right - my mysql is pretty rusty. I'll rework the sample code. Apologies for the confusion.

Collectives™ on Stack Overflow

Nodejs multiple sql query loop

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related