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.

I think you can to change `return callback(null, info);` to `var r={};r[info]=voucher;return callback(null, r);`. Then you get `[{owner_id1:voucher1},{owner_id2:voucher2}]` in your results variable — vp_arth, Aug 05 '13 at 19:05
ok, I'll mull that over. I also was wondering if .push might provide a method to handle the respective JSO's something like rows.push(results.owner_id) ?? — urfx, Aug 05 '13 at 19:23
I don't understand, why you want push ids to vouchers. You'll get `[v1, v2, ... vn, ownID1, ownID2]`. What you want to get? if you want to get `{own_id1: v1, own_id2:v2}` you can try(from results above) `var newResult={}; for(var i=0;i — vp_arth, Aug 05 '13 at 19:53

score 1 · Answer 1 · answered Aug 05 '13 at 20:07

1

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();
 })
}

answered Aug 05 '13 at 20:07

vp_arth

14,461
4
37
66

I've tried several ways to follow your suggestions. I almost got something usable (other than object Object) by using `var r={}; r[JSON.stringify(info)]=voucher; return callback(null, r);` I'm afraid I can't fit your beautified code answer as node doesn't seem to like having a "while" sub routine nested inside compVoucher function and/or in place of async callback. – urfx Aug 05 '13 at 20:57
this `while` with a `rows.pop` is equivalent of `for(...)`)) Don't afraid it) assync.map use something like that too. getOwnerID and connection.query are asynchronous, and we don't get some blocks – vp_arth Aug 05 '13 at 21:57
function awhile(rows, next){ if(!rows.length) return next(); getOID(rows.pop(), function(){ ... }); process.nextTick(function(){awhile(rows, next)}) } awhile(rows, function(){/*All iterations started*/}); – vp_arth Aug 05 '13 at 22:08
this doesn't work. 1. res.end(JSON.stringify(results)); should be res.end(JSON.stringify(result)); and 2. it returns " object Object ". Please see answer below. – urfx Aug 06 '13 at 14:08
Your code really ugly, sorry. Global state crash your application when you get more than one request – vp_arth Aug 06 '13 at 16:16

score 0 · Accepted Answer · answered Aug 06 '13 at 09:30

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.

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

2 Answers2