Lit Aiy Lit Aiy - 3 months ago 11
reST (reStructuredText) Question

Combine output of 2 queries in REST API

I am using node.js restify module. I have a REST API which outputs the result of a single MySQL query in json. Here is the code of the API;

var rest_api_get_list = function (app, url_name) {
function respond(req, res, next) {
var door_mac = req.query.door_mac;

var connection = own_mysql.getMySQL_connection();

var query_str =
"SELECT doors.mac_addr, " +
"sensors.sensor_type " +
"FROM sensors " +
"WHERE (doors.mac_addr = ?) "
;

var query_var = [door_mac];

var query = connection.query(query_str, query_var, function (err, rows, fields) {
//if (err) throw err;
if (err) {
//throw err;
console.log(err);
logger.info(err);
}
else {
res.send(rows);
}
});
return next();
}

app.get(url_name, respond);
};


Suppose I have another query which looks something like this;

var query_str_2 =
"SELECT knobs.mac_addr, " +
"furniture.furniture_type " +
"FROM furnitures" +
"WHERE (knobs.mac_addr = ?) "
;


I want to combine the output of the 2 MySQL queries
query_str
and
query_str_2
and have the REST API return the result in json. How can this be done?

EDIT: To be clearer, I am thinking of the code doing something like this;

var rest_api_get_list = function (app, url_name) {
function respond(req, res, next) {
var door_mac = req.query.door_mac;

var connection = own_mysql.getMySQL_connection();

var query_str =
"SELECT doors.mac_addr, " +
"sensors.sensor_type " +
"FROM sensors " +
"WHERE (doors.mac_addr = ?) "
;

var query_var = [door_mac];

var query = connection.query(query_str, query_var, function (err, rows_output_1, fields) {
//if (err) throw err;
});

var query_str_2 =
"SELECT knobs.mac_addr, " +
"furniture.furniture_type " +
"FROM furnitures" +
"WHERE (knobs.mac_addr = ?) "
;

var query_2 = connection.query(query_str_2, query_var, function (err, rows_output_2, fields) {
//if (err) throw err;

});

//How to display json output of rows_output_1 and rows_output_2?

return next();
}

app.get(url_name, respond);
};

Answer

A module for managing asynchronous control flow - like async - can help you with this.

For example:

function respond(req, res, next) {

    var door_mac = req.query.door_mac;
    var connection = own_mysql.getMySQL_connection();
    var query_var = [door_mac];

    async.parallel([
        function (callback) {

            var query_str =
                "SELECT doors.mac_addr, " +
                "sensors.sensor_type " +
                "FROM sensors " +
                "WHERE (doors.mac_addr = ?) "
            ;
            connection.query(query_str, query_var, callback); 
        },
        function (callback) {

            var query_str_2 =
                "SELECT knobs.mac_addr, " +
                "furniture.furniture_type " +
                "FROM furnitures" +
                "WHERE (knobs.mac_addr = ?) "
            ;
            connection.query(query_str_2, query_var, callback);
        }
    ], function (error, results) {

        // results[0] => result for query_str
        // results[1] => result for query_str_2
        // ... return next() or whatever
    })       
}