recoding nested for loop to use async in node.js

Question

I'm new to Node.js and Async coding. I need to write the equivalent of a nested for loop which will work with Node. I understand that my question is very similar to the one posted here: nested loops asynchronusly in nodejs, next loop must start only after one gets completed, but even after looking at that post in detail, I was unable to fix my code.

I am working with an XML feed. The 'parser' uses the xml2js package. The loop runs exactly as expected if I remove the sql query (for which I'm using the mysql node package), but when I put the sql query in, then all the orders get processed first, the the "DONE" is output, and then the query fails as it tries to look up items for just the last order repeatedly.

I've tried replacing the for loops with async.forEach loops, but this did not help.

Any help or advice on how to recode this in a way more idiomatic to node would be greatly appreciated.

Many thanks! Sixhobbits

    parser.parseString(data, function (err, result) {
    if(err) throw(err);     
    var numOrders = result['Root']['Orders'][0]['Order'].length;
    var curr, currItem, currOrdId, items, sellersCode;
    console.log("Logging IDs of", numOrders, "orders");
    // for each order
    for (var j=0; j<numOrders; j++){
        //current order
        curr = result['Root']['Orders'][0]['Order'][j];         
        currOrdId = curr['OrderId'][0]          
        items = curr['Items'][0]['Item'];
        console.log("Order ID:", currOrdId, "--",items.length, "Items");
        // for each item
        for (var k=0; k<items.length; k++){
            currItem = items[k];
            sellersCode = currItem['SellersProductCode'][0];
            var sqlQuery = 'select data_index, fulltext_id, cataloginventory_stock_item.product_id from catalogsearch_fulltext inner join cataloginventory_stock_item where catalogsearch_fulltext.data_index like "' + sellersCode + '|%"' + 'and cataloginventory_stock_item.item_id = catalogsearch_fulltext.product_id';
            var query = connection.query(sqlQuery,function(err,rows,fields){
                if (err) throw(err);
                console.log("    Item ID          :",currItem['ItemId'][0]);
                console.log("    Full Text ID     :", rows[0]['fulltext_id']);
                console.log("    Product ID       :", rows[0]['product_id']);
            });
        }//for
    }//for
    console.log("DONE");
});//parseString

Answer 1

I realize this is an old post, but you might find this function useful

eachKVAsync = function(elements,userInfo,onKeyValue,ondone) {
    var onDone = ondone;
    var ret = null;
    var done=false;
    var isArray = typeof elements.forEach===$f$; 
    var keys   = isArray ? null     : [], 
        values = isArray ? elements : [];

    if (keys) {
        for (var k in elements) {
            keys.push(k);
            values.push(elements[k]);
        }
    }

    var aborted=false;
    var endLoop = function (userInfo){
        aborted=true;
        if (onDone) {
            onDone(userInfo,aborted);
            onDone = null;
        }
    }


    var i = 0;
    var iterate = function (userInfo) {
        if (i < values.length) {
            var ix=i;
            i++;
            onKeyValue((keys?keys[ix]:i),values[ix],userInfo,iterate,endLoop);          
        } else {
            if (onDone) {
                onDone(userInfo,aborted);
                onDone = null;
                return;
            }
        } 
    } 

    iterate(userInfo);

},

use example

        eachKVAsync(
            elements, {
                aValue: 2004
            },
            function onItem(key, value, info, nextItem, endLoop) {
                if (value.isOk) {
                    info.aValue += value.total;
                    setTimeout(nextItem,1000,info);
                } else {
                    endLoop(info);
                }
            },
            function afterLastItem(info, loopEndedEarly) {
                if (!loopEndedEarly) {
                    console.log(info.aValue);
                }
            }
        );

Answer 2

This is a classic closure-in-a-loop problem. You need to break the closure by passing currItem as an argument:

    for (var k=0; k<items.length; k++){
        currItem = items[k];
        sellersCode = currItem['SellersProductCode'][0];
        var sqlQuery = 'select data_index, fulltext_id, cataloginventory_stock_item.product_id from catalogsearch_fulltext inner join cataloginventory_stock_item where catalogsearch_fulltext.data_index like "' + sellersCode + '|%"' + 'and cataloginventory_stock_item.item_id = catalogsearch_fulltext.product_id';
        var query = connection.query(sqlQuery,(function(CI){
          return function(err,rows,fields){
            if (err) throw(err);
            console.log("    Item ID          :",CI['ItemId'][0]);
            console.log("    Full Text ID     :", rows[0]['fulltext_id']);
            console.log("    Product ID       :", rows[0]['product_id']);
          }
        })(currItem)); // Break closure by passing currItem as argument
    }//for

Answer 3

You were on the right track by looking to use async.forEach. Here's how you would rework this code to use that:

parser.parseString(data, function (err, result) {
    if(err) throw(err);
    var numOrders = result['Root']['Orders'][0]['Order'].length;
    var currOrdId, items, sellersCode;
    console.log("Logging IDs of", numOrders, "orders");
    // for each order
    async.forEach(result['Root']['Orders'][0]['Order'], function (curr, callback1) {
        currOrdId = curr['OrderId'][0];
        items = curr['Items'][0]['Item'];
        console.log("Order ID:", currOrdId, "--",items.length, "Items");
        async.forEach(items, function (currItem, callback2) {
            sellersCode = currItem['SellersProductCode'][0];
            var sqlQuery = 'select data_index, fulltext_id, cataloginventory_stock_item.product_id from catalogsearch_fulltext inner join cataloginventory_stock_item where catalogsearch_fulltext.data_index like "' + sellersCode + '|%"' + 'and cataloginventory_stock_item.item_id = catalogsearch_fulltext.product_id';
            var query = connection.query(sqlQuery,function(err,rows,fields){
                console.log("    Item ID          :",currItem['ItemId'][0]);
                console.log("    Full Text ID     :", rows[0]['fulltext_id']);
                console.log("    Product ID       :", rows[0]['product_id']);
                callback2(err);
            });
        }, callback1);
    }, function (err) {
        console.log("DONE");
    });
});//parseString

Each iteration of async.forEach must call its callback parameter when all of its async processing has completed. You've got two levels in this case which makes it a little more difficult to keep track of in your head, but it's the same concept.