Javascript: Nested Callbacks/MySQL Results

Question

I am sure this is a basic question, but I have been searching google for awhile and can't find a satisfactory answer..

I am used to programming MySQL select queries in PHP and simply grabbing a result, looping through each row, and within the loop doing further queries based on the column values of each individual row.

However, I'm working with javascript server side code now that relies on a SQL object where you pass the query and then a callback function that will be invoked after the query is run.

I'm confused with some of the scoping issues and how to best cleanly do this. For example, I don't want to do something like:

SQL.query("select * from blah", function(result) { 
  for(var i = 0; i < result.length; i++) {
    SQL.query("select * from blah2 where i =" + result[i].property, function(result2) {
      //now how do I access result from here? I know this isn't scoped correctly
    });
  }
});

What is the standard way to write this style of nested SQL query and not have scoping issues/messy code? Thanks!

as everyone has pointed out, your code is syntacticly fine -- but there is a serious issue you should consider, that stems from the asynchronous nature of javascript. Please have a look at my answer below, for the details. — Lee, Nov 30 '10 at 06:05
thanks everyone. as many people noticed, the scoping for result was fine, what was confusing me was the issue people pointed out about i pointing to the final increment count, which was causing issues. I was aware of the asynchronous issue, thanks Lee for providing a workaround. — CP1985, Nov 30 '10 at 06:28

score 0 · Answer 1 · edited May 23 '17 at 11:58

result will be available in the second callback, that's how closures in JavaScript work, the functions has access to all variables in the outer scopes it was defined in.

function outer() {
    var foo = 1;
    function inner() { // inherits the scope of outer
        var bla = 2;
        console.log(foo); // works!

        // another function in here well inherit both the scope of inner AND outer, and so on
    }
    inner();
    console.log(bla); // doesn't work, raises "ReferenceError: bla is not defined"
}
outer();

Now, on to the problem, i will not point to the correct value, it too will be inherited to the second callback but it`s a reference and will therefore has the wrong value.

Fix is to create another closure:

SQL.query("select * from blah", function(result) { 
  for(var i = 0; i < result.length; i++) {
    (function(innerResult) { // anonymous function to provide yet another scope
        SQL.query("select * from blah2 where i =" + innerResult.property, function(result2) {
          // innerResult has the correct value
        });
    })(result[i]); // pass the current result into the function
  }
});

Or an extra function:

function resultThingy(result) {
   SQL.query("select * from blah2 where i =" + result.property, function(result2) {
       // result has the correct value
   });
}

SQL.query("select * from blah", function(result) { 
  for(var i = 0; i < result.length; i++) {
    resultThingy(result[i]);
  }
});

`result` will be available but `i` will not point to the expected index — MooGoo, Nov 30 '10 at 05:36

score 0 · Answer 2 · answered Nov 30 '10 at 05:38

This is very interesting... I've never heard of "server-side javascript"... but none the less this might help organize your code a bit. I use this method to organize my ajax request callbacks.

using your example it would look like this.

SQL.query("select * from some_table", function(result){ runNestedQuery(result); });

function runNestedQuery(result){
  for(var i = 0; i < result.length; i++) {
    SQL.query("select * from blah2 where i =" + result[i].property, function(result2){ nestedResult(result2); });
  }
}

There are no scoping issues with your above code - but this is a nice way I like to organize this kind of thing.

score 0 · Answer 3 · answered Nov 30 '10 at 05:42

Since you are using server-side Javascript, you can likely use forEach. Assuming that result instanceof Array == true:

SQL.query("select * from blah", function(result) { 
  result.forEach(function(item, index) {
    SQL.query("select * from blah2 where i = " + item.property, function(result2) {
      console.log(item, index, result); //works as intended
    });
  });
});

If result is merely array-like, then this

Array.prototype.forEach.call(result, function(item, index) { // etc...

should do the trick.

Lee · Answer 4 · 2010-11-30T05:52:13.303

As others have pointed out result actually will be available all the way down in the nested callback.

But there is a very tricky part to this:

...Because the nested query runs asynchronously, your code will actually fire off a bunch of parallel queries -- one for each row in result -- all running at the same time (!). This is almost certainly not what you want; and unless result is very small indeed, all the simultaneous queries will use up all your available db connections rather quickly.

To remedy this, you might use something like this:

SQL.query("select * from blah", function(result) { 
    handleBlahRow( result, 0 );
});

function handleBlahRow( result, i ) {
    if( !result || (i >= result.length)) return;

    SQL.query("select * from blah2 where i =" + result[i].property, function(result2) {
        // kick off the next query
        handleBlahRow( result, i+1 );

        // result, i, *and* result2 are all accessible here.
        // do whatever you need to do with them
    });
});

The above will run your nested queries 1-at-a-time. It's fairly easy to adapt the above to introduce limited parallelism (eg. 4-at-a-time), if you want it -- though it's probably not necessary.

Javascript: Nested Callbacks/MySQL Results

4 Answers4