NodeJs handling the multiple mysql requests

Question

I am new to nodejs and I am trying to use multiple queries to display result in a single page. I have used async parallel to get the result but I am unable to handle the requests.

This is my callback function

 gettournamentDetail: function (res, req) {
            var connection = mysqlConnectionProvider.getSqlConnection();

            //var test=[];
            var collection = { collection:[] };
            var sqlStatement = "SELECT * FROM tournaments WHERE tournamentId =" + res;
            var tgroup1 = { collection:[]};

            var tournamentdetails = [];
            var sqlStatement2 = "select * from " +
                "(SELECT DISTINCT teamId, teamName,ttournament FROM teams, tournamentTeam WHERE ttournament=" + res + ") as main LEFT JOIN (SELECT DISTINCT groupName,groupTournament FROM groupTeam,tournamentGroup WHERE groupt=tgId) as sub ON sub.groupTournament=main.ttournament";
            // var return_data = {};
            async.parallel([
                function () {

                    if (connection) {
                        connection.query(sqlStatement, function (err, rows, fields) {
                            rows.forEach(function (row) {
                                    tournamentdetails=row;
                                });

                             req(tournamentdetails);


                    });


            }


                },
                function () {
                    var tgroup=[];
                    if (connection) {
                    connection.query(sqlStatement2, function (err, rows, fields) {
                        for (var i in rows) {
                            tgroup.push("group",{
                                teamName: rows[i].teamName,
                                teamId: rows[i].teamId,
                                groupName: rows[i].groupName
                            });
                        }

                            req(tgroup);

                    });
                }

            }

This is my routing page exports.tournamentDetail = function( req, res) {

var tournamentdetail = require('../database/getTournament.js');

tournamentdetail.tournament.gettournamentDetail(req.params.id, function (collection) { console.log(collection); //res.render('tournamentdetail', {title: 'Tournament Detail ', tdetail: collection});

}); };

Any Idea how Can I handle the two requests to display the data. This is how I am getting the data

RowDataPacket {
  tournamentId: 1,
  tournamentUser: 1,
  tournamentDate: 1472299200,
  tournamentLocation: 'Reading',
  tournamentDesc: 'An alternative to renaming app.js is to create an elastic beanstalk configuration file. Add a .config file into the .ebextensions folder, for example, .ebextensions/34.config. Change the NodeCommand setting in the namespace aws:elasticbeanstalk:container:nodejs to whatever command you want to run to start the server. For example, this is a minimal .config file to run npm start instead of app.js:\n\n',
  tournamentInfo: 'Test',
  tournamentCreated: 1471171131,
  tournamentName: 'Ping Pong' }
[ 'group',
  { teamName: 'TeamName', teamId: 1, groupName: 'A' },
  'group',
  { teamName: 'Team2', teamId: 2, groupName: 'A' } ]

Aikon Mogwai Aikon Mogwai · Accepted Answer · 2016-08-21T12:54:34

I little simplify you code, but I don't understand exactly what you needs.

// connect on application start
var connection = mysqlConnectionProvider.getSqlConnection();
if (!connection)
    throw new Error('Smth wrong');
...
let getTournamentDetail = function (tournament_id, callback) { 
    // I think both queries are bad, because they used Cartesian product and distinct
    // Use placeholder to escaping params; it's more safety
    var sql = 'select * from tournaments where tournamentId = ??'; 
    var sql2 = 'select distinct teamId, teamName, ttournament '+
        'from teams, tournamentTeam WHERE ttournament = ??) as main ' + 
        'left join (select distinct groupName, groupTournament ' +
        'from groupTeam, tournamentGroup where groupt = tgId) as sub ' +
        'on sub.groupTournament = main.ttournament';

    async.parallel([
        function (callback) { connection.query(sql, [tournament_id], callback) },
        function (callback) { connection.query(sql2, [tournament_id], callback) }
        ], 
        function (err, results) {
            if (err) 
                return callback(err);

            callback(null, {tournamentdetails: results[0], tgrup: results[1]})
        }
    );  
}
...
var youRouteFunc = function (req, res) {
   getTournamentDetail(req.params.id, function (err, data) {
       if (err)
           return res.send(err.message);

       res.render('tournamentdetail', {title: 'Tournament Detail ', tdetail: data});
   });
}

NodeJs handling the multiple mysql requests

1 Answers