Node JS MySQL query dependent on another query

Question

In the following code, I have a select query called queryString which retrieves information about some tweets that are already stored in the database. I want to retrieved the hashtags that are mentioned in those tweets so that I show to a user the tweet information along with the its hashtags:

var queryString = 'select Tweet.Label, Tweet.TDate, Tweet.TLink, Author.Lable, Author.ALink from Tweet, Author where Tweet.AuthorID IN (select ID from Author where Lable = ?) AND Author.ID IN (select ID from Author where Lable = ?)';
var query = connection.query(queryString, [term,term], function(err, rows) {
        console.log(rows);
        //res.write(JSON.stringify(rows));
        var tweet = JSON.parse(JSON.stringify(rows));
        for(var i in tweet){
            res.write("Author: ");
            res.write("<a href='" + tweet[i].ALink + "' target='_blank'>" + tweet[i].Lable + "</a> <br/>");
            res.write("Date: " + tweet[i].TDate  + "<br/>");
            res.write("Tweet: " + "<a href='" + tweet[i].TLink + "' target='_blank'>" + tweet[i].Label + "</a> <br/>");
            var query1 = connection.query('select Label from Hashtag where ID IN (select HashID from tweethashs where TweetID IN (select ID from Tweet where Label = ?))', [tweet[i].Label], function(err, rows1) {
                    var tweet1 = JSON.parse(JSON.stringify(rows1));
                    for(var i in tweet1){
                        res.write("Hashtag: ");
                        res.write(tweet1[i].Label);
                    }
                }
            );
            res.write("<br/><br/>");
        }

        res.end();
    });

What I did is I included a query for the hashtags inside the loop of tweets so that I take the tweet as a parameter for the hashtags query (in where clause). When I run the code, I got the following error:

events.js:154
      throw er; // Unhandled 'error' event
      ^

Error: write after end
    at ServerResponse.OutgoingMessage.write (_http_outgoing.js:426:15)
    at Query._callback (C:\Users\Nasser\Desktop\Spring Semester\Intelligent Web\
Node JS\SocialSearch.js:58:11)
    at Query.Sequence.end (C:\Users\Nasser\Desktop\Spring Semester\Intelligent W
eb\Node JS\node_modules\mysql\lib\protocol\sequences\Sequence.js:96:24)
    at Query._handleFinalResultPacket (C:\Users\Nasser\Desktop\Spring Semester\I
ntelligent Web\Node JS\node_modules\mysql\lib\protocol\sequences\Query.js:144:8)

    at Query.EofPacket (C:\Users\Nasser\Desktop\Spring Semester\Intelligent Web\
Node JS\node_modules\mysql\lib\protocol\sequences\Query.js:128:8)
    at Protocol._parsePacket (C:\Users\Nasser\Desktop\Spring Semester\Intelligen
t Web\Node JS\node_modules\mysql\lib\protocol\Protocol.js:280:23)
    at Parser.write (C:\Users\Nasser\Desktop\Spring Semester\Intelligent Web\Nod
e JS\node_modules\mysql\lib\protocol\Parser.js:73:12)
    at Protocol.write (C:\Users\Nasser\Desktop\Spring Semester\Intelligent Web\N
ode JS\node_modules\mysql\lib\protocol\Protocol.js:39:16)
    at Socket.<anonymous> (C:\Users\Nasser\Desktop\Spring Semester\Intelligent W
eb\Node JS\node_modules\mysql\lib\Connection.js:96:28)
    at emitOne (events.js:90:13)

Can someone help me solving this problem

Answer 1

If you choose to do this by stalling the res.end() you are not sure whether your response will be written in the same order as the tweets are in the array.

The for loop is synchronous but the queries are asynchronous which means they could finish faster than some which started earlier. Which will result theoretically in the writes to happen at random.

What you can use is an async.map.

Basically, instead of that foreach loop you can put something like the following code:

Note: To better understand this first read the async.map code.

//the tweet for which I get the information from DB
var getTweetInfo = function(tweet, callback){
    con.query(query, function(err, tweetInformation){
        if(err) {
            console.log(err);
            //with this you are passing the error back into the async.map function. Otherwise it would be lost (Bad javascript :P)
            callback("Error getting tweets" + err);
        }
        else{
            //in here you can make any changes to the tweet information before sending it as a response

            //this will send the tweetInformation into the results array
            callback(null, tweetInformation);
        }
    });
}

//tweetsArray is the array with tweets
//getTweeetInfo is a function that will get each tweet from the tweets array and will apply the query to it 
async.map(tweetsArray, getTweetInfo, function(error, tweetInformationArray){
     if (error) {
         console.log(error);
     }else{
         //'results' will hold an array with the information for each tweet in the same order as the initial tweets
         res.json({
             response: tweetInformationArray
         });
     }
})

After this you can parse the response on the front-end. The data is consisted, asynchronous (you do not keep the event loop waiting), in order which means you can go through it and display it nicely on the screen.

Answer 2

Basically you make several asynchronous db queries in your for loop, but you close your response when the synchronous for loop is ending.So when the db queries end you try to write on an response which is already closed.

    var queryString = 'select Tweet.Label, Tweet.TDate, Tweet.TLink, Author.Lable, Author.ALink from Tweet, Author where Tweet.AuthorID IN (select ID from Author where Lable = ?) AND Author.ID IN (select ID from Author where Lable = ?)';
        var query = connection.query(queryString, [term,term], function(err, rows) {
        console.log(rows);
        //res.write(JSON.stringify(rows));
        var tweets = JSON.parse(JSON.stringify(rows));
        var queries_made = 0;
        var queries_success = 0;
        tweets.forEach(function(tweet){
            connection.query('select Label from Hashtag where ID IN (select HashID from tweethashs where TweetID IN (select ID from Tweet where Label = ?))', [tweet.Label], function(err, rows1) {
                    res.write("Author: ");
                    res.write("<a href='" + tweet.ALink + "' target='_blank'>" + tweet.Lable + "</a> <br/>");
                    res.write("Date: " + tweet.TDate  + "<br/>");
                    res.write("Tweet: " + "<a href='" + tweet.TLink + "' target='_blank'>" + tweet.Label + "</a> <br/>");

                    var tweet1 = JSON.parse(JSON.stringify(rows1));
                    for(var j in tweet1){
                        res.write("Hashtag: ");
                        res.write(tweet1[j].Label);
                    }
                    res.write("<br/><br/>");
                    queries_success++;
                    if(queries_made==queries_success)
                        res.end();
                }
            );
            queries_made++;

        })
        
    });

I have added two counter that will track the db queries you requested and the queries that have replied. When those two counters are equal all db transactions and response writes are completed, so you can close the response stream.

Although this is not best practice. You should check the async module or try to use promises with your db queries.

Edit I changed the code so that the writes occure when the inside queries complete. Be careful now because the order of the writes will not respect the order of the first query but it will write based on what inside query ends first. I must tell you that this is not the best way to accomplish that and you need to read the async module and the promises i told you before. Also take a look at how to mix asynchronous and synchronous operations in javascript!