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.
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.