Async.waterfall not passing callback for nested mysql queries?

Question

I have a series of nested mysql queries that I need to execute in series, as later queries rely on the result of the earlier ones - async.waterfall seems like the perfect solution. However, the second step of the waterfall is failing to append its result to my array:

async.waterfall([
function(callback) {
    connection.query(query, function(err, rows, fields) {
        if (err) throw err;

        var top_ten_publishers = [];
        rows.forEach(function (result) {
            var publisher_row = [result.name, result.sale_amount, result.actual_commission, result.transactions, result.post_date, result.toolbar_id, result.shop_source];

            top_ten_publishers.push(publisher_row);
        })
        callback(null, top_ten_publishers);
    })
},
function(top_ten_publishers, callback) {
    top_ten_publishers.forEach(function (publisher_row) {
        connection.query(“select  sum(sale_amount) as 'sale_amount', sum(actual_commission) as 'actual_commission', count(*) as transactions, from table where mall_name = '" + publisher_row[0] + "' and fc_post_date between '" + start_date_wow + "' and '" + end_date_wow + "' Group by name order by sum(sale_amount) desc;", function (err, rows, fields) {
            rows.forEach(function (result) {
                var wow = (publisher_row[3] - result.sale_amount) / result.sale_amount;
                publisher_row.unshift(wow);
            })
        });
    })
    callback(null, top_ten_publishers);
}
], function (err, result) {
    console.log(result);
});

If I put a console.log inside the second step of the waterfall, I see the new values being correctly prepended to the arrays, but when the final step runs, the new values aren't in the array. Am I doing something async inside the second step that lets the callback get called before the queries are run?

Answer 1

In your second function top_ten_publishers forEach makes asynchronous calls in each iteration after iterations are complete and then the function exits. Since there is no guarantee that async calls to async.query are also complete you end up with messed up results.

Try this modified version of top_ten_publishers.

function(top_ten_publishers, callback) {
    top_ten_publishers.forEach(function (publisher_row) {
        connection.query(“select  sum(sale_amount) as 'sale_amount', sum(actual_commission) as 'actual_commission', count(*) as transactions, from table where mall_name = '" + publisher_row[0] + "' and fc_post_date between '" + start_date_wow + "' and '" + end_date_wow + "' Group by name order by sum(sale_amount) desc;", function (err, rows, fields) {
            rows.forEach(function (result) {
                var wow = (publisher_row[3] - result.sale_amount) / result.sale_amount;
                publisher_row.unshift(wow);
            })
            callback(null, top_ten_publishers);
        });
    })
}