Node Mysql async multiple queries

Question

I was wondering what's the best way to handle nested mysql-queries is nodejs.

So something like this:

connection.query("SELECT * FROM blogs ORDER BY time DESC", function(err, blogs, fields) {

   for (blog in blogs) {

        connection.query("SELECT * FROM tags WHERE blog_id='blog.id' ", function(err, tags, fields) {

        blog.tags = tags

     });

   }

   res.send(blogs)

});

This obviously doesn't work, because of the async nature. The result already gets returned before the tags are fetched.

I've been reading up on node and callbacks and promises seems to be the way to go. But I'm unable to see how I would best use them in this small example.

Thx!

This would be a good opportunity to start using a promise library like Q, in this case it's Q.spread method (or maybe these reduction examples — Bojangles
– Bojangles, Commented Jun 15, 2014 at 19:52

Alexei · Accepted Answer · 2014-06-15 20:12:31Z

So you have to wait for all callbacks to return before you send the response. If we ignore error handling and empty results for simplicity this can be done similar to:

var callback = function(blogs) {
    res.send(blogs);
}

connection.query("SELECT * FROM blogs ORDER BY time DESC", function(err, blogs, fields) {
    var pending = blogs.length;

   for (blog in blogs) {

        connection.query("SELECT * FROM tags WHERE blog_id='blog.id' ", function(err, tags, fields) {
        blog.tags = tags;

        if (0 === --pending) {
            callback(blogs);
        }
     });
   }
});

With promises, look into Promise.all function which returns a new promise. This promises is resolved when all promises passed to it in the array are resolved. With the Q library it should be something like:

var getTags = function(blog) {
    var deferred = Q.defer();
    connection.query("SELECT * FROM tags WHERE blog_id='blog.id' ", function(err, tags, fields) {
        blog.tags = tags;
        deferred.resolve();
    });
    return deferred.promise;
}

var promises = blogs.map(getTags(blog));

Q.all(promises).then(res.send(blogs));

randomness · Accepted Answer · 2015-06-22 06:36:31Z

You can use async.series module(to iterate over the queries`)

async.eachSeries(blogs,
                function (query, callback) {

                    connection.query(blogs.blog, "SELECT * FROM tags WHERE blog_id='blog.id'" , function (err, result) {
                        if (err) {

                            //throw err;
                            callback(err, null);
                            return;

                        } else {
                            console.log('Query executed successfully');
                            blogs.blog.tags = tags;
                        }
                        callback(null, blogs.blog);

                    });

                },

                function finalCallback(err, results) {

                            return callback(null, results);
                        });
                    }
                });

        });



    });`

You can enhance this with and query.on feature (to process each row)

vmx · Accepted Answer · 2014-06-16 04:59:33Z

2

You can try something like this;

connection.query("SELECT * FROM blogs ORDER BY time DESC", fetchedBlogs);

function fetchedBlogs(err, blogs, fields) {

    if (err || !blogs || !blogs.length) {
        // an error occurred or no blogs available
        // handle error or send empty blog array
        return res.send();
    }

    var count = blogs.length;

    (function iterate(i) {
            if (i === count) {
                // all blogs processed!
                return res.send(blogs);
            }

            var blog = blogs[i];

            connection.query("SELECT * FROM tags WHERE blog_id='" + blog.id +
                "'", fetchedTags);

            function fetchedTags(err, tags, fields) {
                blog.tags = tags;
                iterate(i+1);
            });
    })(0);
}

Hope this helps.

edited Jun 16, 2014 at 4:59

answered Jun 15, 2014 at 19:48

vmx

8,5074 gold badges24 silver badges20 bronze badges

1 Comment

vmx Over a year ago

Why is this answer downvoted? I agree ideal solution to this problem would be to fetch both data in a single query. This solution works if the two query are needed, this snippet also shares a neat way to handle callbacks in a loop without using a flow control library like async/promise/etc.

Collectives™ on Stack Overflow

Node Mysql async multiple queries

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related