mysql - 使用nodeJS解析多个表上mysql select查询的结果

Question

我是 nodeJS 初学者，我正在尝试通过创建博客来学习它。为此，我有三个表

CREATE TABLE `articles` (
 `article_id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(255) NOT NULL,
 `content` longtext NOT NULL,
 `image` varchar(255) NOT NULL,
 `created` datetime NOT NULL,
 `author_id` int(11) NOT NULL,
 PRIMARY KEY (`article_id`)
)

CREATE TABLE `authors` (
 `author_id` int(11) NOT NULL AUTO_INCREMENT,
 `email` varchar(255) NOT NULL,
 PRIMARY KEY (`author_id`)
)

CREATE TABLE `comments` (
 `comment_id` int(11) NOT NULL AUTO_INCREMENT,
 `comment_content` longtext NOT NULL,
 `created` datetime NOT NULL,
 `comment_author` varchar(255) NOT NULL,
 `id_article` int(11) NOT NULL,
 PRIMARY KEY (`comment_id`)
)

在我的页面上，我想获取我所有的文章，以及它们的相关作者和评论。

这是我获取数据的 Node 代码:

app.get('/api/articles', function(req, res){
    connection.query('SELECT * FROM articles LEFT JOIN authors ON articles.author_id = authors.author_id LEFT JOIN comments ON articles.article_id = comments.id_article', function(err, row, fields){
        if(!err){
            res.json(rows);
        }else
            console.log('Error');
    });
});

这个查询返回我需要的数据，但我想解析它以获得我可以在前面部分更容易使用的东西，比如

[
  {
    article_id: 1,
    content: 'test',
    title: 'test',
    image: '',
    author: {
      author_id: 1,
      email: 'test@test.com'
    },
    comments: [
      {
        comment_id: 1,
        comment_content: 'test',
        comment_author: 'test'
      },
      {
        comment_id: 2,
        comment_content: 'test',
        comment_author: 'test'
      }
    ]
  }
]

而不是当前返回的那个样子

[
  {
    article_id: 1,
    title: 'test',
    content: 'test',
    image: '',
    author_id: 1,
    email: 'test@test.com',
    comment_id: 1,
    comment_content: 'test',
    comment_author: 'test
  }
]

我花了一些时间寻找可以做的事情，但找不到任何东西，所以如果有人知道怎么做，我将不胜感激。

谢谢

Answer 1

您需要做两件事: (1) 确保您在查询中按 article_id 排序 (2) 创建一个微型状态机，跟踪 article_id，并遍历每条记录以聚合评论。如果你的 article_id 改变了，将记录写到表中，然后继续下一篇文章:

var table = [];

var lastid = -1;

var article = {};

for(var i=0;i<rows.length;i++) {

    var row = rows[i];

    if (row.article_id!==lastid) {
        //The id has changed, so create a new article

        if (article.article_id) {
            //If this isnt the first time looping, add the last article to the table
            table.push(article);    
        }

        article = {};    

        //create the structure you want
        article.article_id = row.article_id;
        article.title = row.title,
        article.content = row.content,
        article.image = row.image,

        article.author = {
            author_id: row.author_id,
            email: row.email,
        };

        //comments go in this array.  add the first one
        article.comments = [{
            comment_id:row.comment_id,
            comment_content:row.commment_content,
            comment_author:row.comment_author
        }];

    } else {

        //same article, new comment
        article.comments.push({
            comment_id:row.comment_id,
            comment_content:row.commment_content,
            comment_author:row.comment_author
        })

    }

    //update the id to check against the next row
    lastid = row.article_id;

}

//make sure you push on the last article
table.push(article);

//Now you can send back the table in the new structure...
return table;