how to join the result of two node-mysql.js functions in express.js

Question

I'm trying to export some legacy data from a mysql db as JSON using express and node-mysql. The SQL below works fine. I'm struggling with a simple way to join the 'result' of getOwnerID and to the data for each row returned in compVouchers.

I'm also using async.js having followed another thread, though I'm not sure this is helping. But if I can get away with not using this that might be better.

  //join some tables to get comprehensive voucher data 
    exports.compVouchers = function(req, res) {
        var advertType = '"discount_voucher_all_CANCELLED"';
        if (connection) {
        connection.query('SELECT V.id AS voucher_id, V.title, V.description, V.discount, V.customers_total, V.advert_type, ' +
            'V.customers_redeemed, V.start_date, V.expiry_date, V.redemption_code, ' +
            'K.image, G.latitude, G.longitude FROM '+dbname+'.vouchers AS V ' +
            'LEFT JOIN '+dbname+'.iag_key_tags AS K ON ( V.id = K.id ) ' +
            'LEFT JOIN '+dbname+'.iag_geo_tags AS G ON ( V.id = G.id ) ' +
            'WHERE V.advert_type like '+advertType , function(err, rows, fields) {
                if (err) throw err;
                console.log("Got "+rows.length+" Vouchers:");
               // now get each vouchers owner id
                async.map(rows, getOwnerID, function(err, results){
                res.writeHead(200, {'Content-Type': 'text/plain'});
                res.end(JSON.stringify(results));
                res.end();
                });

            });

        }

    };

        function getOwnerID(voucher, callback) {
            connection.query('SELECT parent_tagid AS owner_id FROM '+dbname+'.iag_key_tag_relationships WHERE TYPE =2 AND tagid =  '+ voucher.voucher_id,  function(err, info) {
                if(err) {
                    console.log(err);
                    return callback(err);
                }
                else {
                    return callback(null, info);
                }
            });
        }

so

res.end(JSON.stringify(results)); // prints all the owner_id of each voucher only

res.end(JSON.stringify(rows)); // prints the data for each voucher but not the owner_id

Combining node-mysql result rows into single JSON return for node.js doesn't solve the problem but as you can see I have tried to follow the suggestion in that thread.

Answer 1

OK guys (thanks @vp_arth for nudging me in an interesting direction that got me close, the typo results should be result btw)

So anyhow, I ended up with a hack solution, I'm using .push underscore.js and .replace to help me clean up the JSON data so I could use it later / next in a nosql database like MongoDB.

   //declare global array variable... there must be a more elegant solution
    var compV = [];
    exports.compVouchers = function(req, res) {
        var advertType = '"discount_voucher_all_CANCELLED"';
        if (connection) {
        connection.query('SELECT V.id AS voucher_id, V.title, V.description, V.discount, V.customers_total, V.advert_type, ' +
            'V.customers_redeemed, V.start_date, V.expiry_date, V.redemption_code, ' +
            'K.image, G.latitude, G.longitude FROM '+dbname+'.vouchers AS V ' +
            'LEFT JOIN '+dbname+'.iag_key_tags AS K ON ( V.id = K.id ) ' +
            'LEFT JOIN '+dbname+'.iag_geo_tags AS G ON ( V.id = G.id ) ' +
            'WHERE V.advert_type like '+advertType , function(err, rows, fields) {
                if (err) throw err;
                // now get each vouchers owner id
                console.log("Got "+rows.length+" Vouchers:");
                async.each(rows, getOwnerID, function(err, results){
                res.writeHead(200, {'Content-Type': 'text/plain'});
                // now user underscore.js to clean up JSON
                var finalComp = JSON.stringify(un.flatten(un.compact(compV)));
                // finally use replace to customise the known output to merging the voucher and owner into single JSON documents
                var finalComp2 = finalComp.replace(/},{"owner_id/g,',"owner_id'); //not happy with this but it works
                res.write(finalComp2);
                res.end();
                });

            });

        }

    };

        function getOwnerID(voucher, callback) {
            connection.query('SELECT parent_tagid AS owner_id FROM '+dbname+'.iag_key_tag_relationships WHERE TYPE =2 AND tagid =  '+ voucher.voucher_id,  function(err, owner) {
                if(err) {
                    console.log(err);
                    return callback(err);
                }
                else {
                    var arr = [];
                    arr.push(voucher);
                    arr.push(owner);
                    compV.push(arr);  //append to global array variable
                    return callback(null, compV); // doesn't return anything??
                }
            });
        }

perhaps there is a more elegant way to merge

[{"F1_field1":"F1_value1","F1_field2":"F1_value2"},{"F2_field1":"F2_value2"}]

into

[{"F1_field1":"F1_value1","F1_field2":"F1_value2","F2_field1":"F2_value2"}]

here is my final code with comments / thoughts

you would now also need to npm install underscore addition to async and declare them in variables... not to mention node-mysql and express... I have used "un" instead of "_" so I don't get confused with code that might look like jquery shorthand later.

Answer 2

Here this is some more beauty than in coments :) Try this:

var result={}, c=rows.length;
function getOwnerID(voucher, cb){
           connection.query('SELECT ...',  function(err, info) {
                if(err) console.log(err);
                else result[info] = voucher;
                if(!--c)return cb();
            });
}

while(rows.length){
 getOwnerID(rows.pop(), function(){
  res.writeHead(200, {'Content-Type': 'text/plain'});
  res.end(JSON.stringify(results));// {"owner1":{voucher1}, "owner2":{voucher2}}
  res.end();
 })
}