Combine output of 2 queries in REST API

Question

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?

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

EDIT: I started a bounty to attract answers which use Promises.

Fivefinger

Are you asking how to do two queries or are you really asking a SQL question about how to do joins? – I

HeadCode, Sorry for not being clear. This is not a SQL question. I am asking how to do 2 queries and returning the combined result of the 2 queries in json. I have edited the question to make it clearer. Thank you. – Fivefinger

The tricky part to this problem is the asynchronous nature of node.js. The queries cannot run one after the other. It will be interesting to see how Promises or modules like bluebird are used to answer this question. – Calculate

Answer 1

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

Sarcomatosis

Thanks. Upvoted. I will try it out. Would you happen to know how the solution can use Promises or something like bluebird module? – Fivefinger

The code in your question involved a callback-based API, so my answer took that into account. Yes, you could use promises, but you'd need to use something like bluebird's promisify to convert the callback-based API to a promise-based API. Once you have an API that returns promises, you'd do a Promise.all to 'combine' the results. – Sarcomatosis

Thanks. I guess your solution using callbacks and async would be the neatest since the original code involved a callback-based API. – Fivefinger

Answer 2

If you are asking for parallelism, as @cartant says, you can use async or (much better) promises (Promise.all(p1, p2).then(...)).

But if you are asking for optimal approach (parallelism gives faster results at the cost of more resources per actual result consumption), you can do it in a single query instead:

var query_str =
    "SELECT doors.mac_addr, " +
    "sensors.sensor_type as type " +
    "FROM sensors " +
    "WHERE (doors.mac_addr = ?) " +
    "UNION " +
    "SELECT knobs.mac_addr, " +
    "furniture.furniture_type as type " +
    "FROM furnitures" +
    "WHERE (knobs.mac_addr = ?) "
;

This way you perform single database call and, of course, database won't perform any parallelism but, instead, it will make its best effort to get the answer at the best time with the lower resource consumption.

So, if you have to attend many more request, the average efficiency will be better even, on the other hand, if you are asking for faster results in an environment where the resources doesn't much care, then parallel approach will be better.

Sparkle

Merging the results is costly as well. It could be that with huge amount of data it will be faster to do a single query then two in parallel. – Extrauterine

So true. At least for a simple union. More complicated merging patterns may be easier to optimize in parallel. Specially when not all queries (of the separate version) are huge. But, in general, its true: handling huge data transformations in memory is like pain in the ass ;-) – Sparkle