gistfile1.js

/**
 * Example reading multiple databaserequests simultaniously
 * while calling the callback-Fn only when all request have finished
 *
 * In this example, we want to read a table with all area's in a specific country.
 * From there on, from every area we need to know the citienames and population, which resides in a second table.
 * 
 * In some situations, this might be done with one statement using INNER JOIN, but there are cases where you need multiple requests. (performance or limitation)
 *
 * The array is build, has this form:
 * 
 * [
 *     {
 *         'areaid' : 1,
 *         'name' : 'Gelderland',
 *         'cities' : [
 *             {
 *                 'id' : 42,
 *                 'cityname' : Arnhem,
 *                 'population' : 70.000
 *             },
 *             {
 *                 'id' : 26,
 *                 'cityname' : Nijmegen,
 *                 'population' : 80.000
 *             }
 *     },
 *     {
 *         'areaid' : 2,
 *         'name' : 'Overijssel',
 *         'cities' : [
 *             {
 *                 'id' : 21,
 *                 'cityname' : Enschede,
 *                 'population' : 90.000
 *             },
 *             {
 *                 'id' : 18,
 *                 'cityname' : Hengelo,
 *                 'population' : 40.000
 *             }
 *     }
 * ]
 * 
*/


YUI.add('MyModelFoo', function(Y, NAME) {

    var mysqlClient =require('mysql');
    var mysqlConfig = {
        host : 'localhost',
        port : 3306,
        user : 'myusername',
        password : 'mypassword',
        database : 'mydbname'
    };

    Y.namespace('mojito.models')[NAME] = {

        getData: function(selectcountry, callback) {
            var queryString = 'SELECT areaid, name FROM areas WHERE country="' + selectcountry + '" ORDER BY name';
            // mysqlConnection.query() will automaticly invoce mysqlConnection.connect()
            var mysqlConnection = mysqlClient.createClient(mysqlConfig);
            mysqlConnection.query(queryString, function selectCb(error, results, fields) {
                mysqlConnection.end();
                if (error) {
                    // NO connection is set
                    console.log(error.message);
                    callback(error, null);
                }
                else {
                    var queryString,
                        i,
                        mysqlConnectionsDeep = [],
                        stack = new Y.Parallel();
                    for (i=0; i<results.length; i++) {
                        queryString = 'SELECT * FROM cities WHERE areaid=' + results[i].aeraid + ' ORDER BY cityname';
                        mysqlConnectionsDeep[i] = mysqlClient.createClient(mysqlConfig);
                        mysqlConnectionsDeep[i].query(
                            queryString, 
                            stack.add(Y.rbind(function selectCb(errorDeep, resultsDeep, fieldsDeep, masterrecord, mastererror, index) {
                                mysqlConnectionsDeep[index].end();
                                if (errorDeep) {
                                    // NO connection is set
                                    console.log(error.message);
                                    mastererror = errorDeep;
                                }
                                else {
                                    // this.results is in fact the results of the first query
                                    masterrecord.cities = resultsDeep; 
                                }
                            }, this, results[i], error, i))
                        ); 
                    }           
                    stack.done(Y.bind(callback, null, error, results));
                }
            });            
        }

};

}, '0.0.1', {requires: ['parallel']});

Hey mridgway,

I started with your inspiration which lead me to using Y.parallel().
I never used that before, but I like to keep as much as posible to using YUI3-modules. (realy good code, well documented and I'm used to used them).

So this lead me to the code I've posted here.
I've tested it and it works pretty well.

One thing to notice, I don't know what will happen when a mysql-error occurs: I didn't test that.
Especially 'this.end();' inside mysqlClient: does 'this' refer to the clientconnection? The first one should do, but the nested one I'm not sure.

I felt like posting, because I can imagine it is a case other might be needed as well.
So better to document. Code is a bit changed from what I used, to make it a better sample (hope no errors came into it).

Regards,
Marco Asbreuk.

Your nested this will not be what you expect. I would recommend setting a local variable inside your second query() call like var self = this; and then later use self.end().

I was afraid so...

But I'm not sure I understand how this could help.
Inside the second querycall, I'm at the deepest level. It would mean something like:
var self=this;
self.end(); straight afterwards.

Could you show me by modifying the code?

Regards,
Marco.