在下面的代码中,我有一个名为queryString
的选择查询,它检索一些已经存储在数据库中的推文的信息。我想检索这些推文中提到的标签,以便向用户显示推文信息及其标签:
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();
});
我所做的是在tweet循环中包含一个对标签的查询,这样我就可以将tweet作为标签查询的参数(在where子句中)。当我运行代码时,我得到了以下错误:
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:UsersNasserDesktopSpring SemesterIntelligent Web
Node JSSocialSearch.js:58:11)
at Query.Sequence.end (C:UsersNasserDesktopSpring SemesterIntelligent W
ebNode JSnode_modulesmysqllibprotocolsequencesSequence.js:96:24)
at Query._handleFinalResultPacket (C:UsersNasserDesktopSpring SemesterI
ntelligent WebNode JSnode_modulesmysqllibprotocolsequencesQuery.js:144:8)
at Query.EofPacket (C:UsersNasserDesktopSpring SemesterIntelligent Web
Node JSnode_modulesmysqllibprotocolsequencesQuery.js:128:8)
at Protocol._parsePacket (C:UsersNasserDesktopSpring SemesterIntelligen
t WebNode JSnode_modulesmysqllibprotocolProtocol.js:280:23)
at Parser.write (C:UsersNasserDesktopSpring SemesterIntelligent WebNod
e JSnode_modulesmysqllibprotocolParser.js:73:12)
at Protocol.write (C:UsersNasserDesktopSpring SemesterIntelligent WebN
ode JSnode_modulesmysqllibprotocolProtocol.js:39:16)
at Socket.<anonymous> (C:UsersNasserDesktopSpring SemesterIntelligent W
ebNode JSnode_modulesmysqllibConnection.js:96:28)
at emitOne (events.js:90:13)
有人能帮我解决这个问题吗?
基本上,您在for循环中进行了几个异步数据库查询,但在同步for循环结束时关闭了响应。因此,当数据库查询结束时,您可以尝试对已经关闭的响应进行写入。
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++;
})
});
我添加了两个计数器,用于跟踪您请求的数据库查询和已回复的查询。当这两个计数器相等时,所有数据库事务和响应写入都将完成,因此您可以关闭响应流。
尽管这不是最佳实践。您应该检查异步模块,或者尝试在数据库查询中使用promise。
编辑我更改了代码,以便在内部查询完成时进行写入。现在要小心,因为写入的顺序将不尊重第一个查询的顺序,而是根据内部查询首先结束的内容进行写入。我必须告诉你,这不是实现这一点的最佳方式,你需要阅读异步模块和我之前告诉你的承诺。还要看看如何在javascript中混合异步和同步操作!
如果您选择通过暂停res.end()来完成此操作,您不确定您的响应是否会按照与tweet在数组中相同的顺序写入。
for循环是同步的,但查询是异步的,这意味着它们可以比之前开始的查询更快地完成。从理论上讲,这将导致写入是随机发生的。
您可以使用async.map.
基本上,您可以放置类似以下代码的东西来代替foreach循环:
注意:要更好地理解这一点,请首先阅读async.map代码。
//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
});
}
})
之后,您可以在前端解析响应。数据是按顺序组成的、异步的(您不需要等待事件循环),这意味着您可以遍历它并在屏幕上很好地显示它。