Parse the result of a mysql select query on multiple tables with nodeJS

Question

I'm a nodeJS beginner and am trying to learn it by creating a blog. To do so, I have three tables

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`)
)

On my page, I want to get all my articles, with their associated authors and comments.

This is my node code to get the data :

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

This query returns the data I need, but I want to parse it to get something that I can use easier in the front part, like

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

Instead of the current return that looks like

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

I spent some time looking for something to do it, but couldn't find anything, so if someone knows how to do it, I'd be very grateful.

Thanks

binarymax · Accepted Answer · 2019-10-14 19:21:55Z

1

You'll need to do two things: (1) make sure you are sorting by article_id in your query (2) create a tiny state machine, keeping track of the article_id, and loop through each record aggregating the comments. if your article_id changes, write the record to the table and move on to the next article:

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;

edited Oct 14, 2019 at 19:21

answered Mar 7, 2016 at 18:32

binarymax

3,4353 gold badges22 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Angulax Over a year ago

Worked great, thank you ! However, is there any module or anything that can make the process easier ? It's ok with small tables like these ones, but it might be a little too much for bigger projects.

binarymax Over a year ago

I am not familiar with any library that can easily do this. For long tables is to do the same approach above, but enhance it by using a stack for your lastid and object type or level. Many years ago I wrote a basic tree based approach, but it is very simple and may need to change based on your requirements: github.com/binarymax/jstrees/blob/master/public/javascripts/…

Collectives™ on Stack Overflow

Parse the result of a mysql select query on multiple tables with nodeJS

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related