Bharat Paudyal Bharat Paudyal - 3 months ago 21
Node.js Question

NodeJs handling the multiple mysql requests

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' } ]

Answer

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