In my node.js code using express, I have set up a route to request data from a mysql database. My goal is to pass the returned JSON in tabular form to another function to restructure it into a hierarchy type JSON.
I have individually tested the script to restructure the SQL query output. However, I am facing difficulties passing it from my query function to the new script(function).
I can't seem to figure out what I am doing wrong. Any assistance would be greatly appreciated. Thank you.
exports.get_site_menu = function (req, res) {
var dbc;
console.log('In menu setup');
async.waterfall([
// get a connection
function (callback) {
db.db(callback);
}
,
querylookup, modifyjson
], completed);
function querylookup(dbclient, res) {
dbc = dbclient;
dbc.query("SELECT categories, " +
"subcategories, " +
"pid, " +
"title, " +
"description " +
"FROM MENU_SELECT_ACTIVE_VIEW " +
"where company_id = ? and site_id = ?", [req.query.companyid, req.query.siteid], res);
}
function modifyjson(err, res) {
categories = [];
console.log('results ' + res);
res.forEach(function (entry) {
var cindex = categories.map(function (category) {
return category.name;
}).indexOf(entry.categories);
console.log(cindex);
if (cindex < 0) {
// Not found in categories array
cindex = categories.push({
name: entry.categories,
subcategories: []
}) - 1; // -1 to fix the index
}
// Lets search the subcategory
var category = categories[cindex];
var sindex = category.subcategories.map(
function (subcategory) {
return subcategory.name;
}
).indexOf(entry.subcategories);
if (sindex < 0) {
// Not Found
sindex = category.subcategories.push({
name: entry.subcategories,
items: []
}) - 1;
}
// Subcategory exists. Just push
category.subcategories[sindex].items.push({
pid: entry.pid,
description: entry.description,
title: entry.title
});
});
menu = {
menu: {
categories: categories
}
};
console.log('menu ' + menu);
}
function completed(err, menu, fields) {
if (dbc) dbc.end();
if (err) {
callback(err);
} else {
console.log(menu);
res.contentType('json');
res.send(JSON.stringify(menu));
}
}
};