Nasser Nasser - 1 month ago 13
MySQL Question

Node JS MySQL query dependent on another query

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

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!