NodeJS running a promise-mysql query inside a foreach loop waiting for result each time

Question

I am trying to query a mysql database twice, the second time, multiple times for each result from the first time but I am unable to work out how to wait for the result from the second query before continuing the

 admin.get('/', function (req, res) {
        mysqlQ("SELECT I.ID as ID, C.ID AS CID, I.PD AS PostDate, C.Name AS CatName, U.UserName AS UserName, I.Title AS Title, I.Post AS PostData FROM categories AS C JOIN INAT AS I ON C.ID = I.Category JOIN Users AS U on U.ID = I.User").then(function (INAT) {
        INAT.forEach(function (team) {
            team.time = moment(team.PostDate).format("DD-MM-YYYY HH:mm:ss");
            team.TS = moment(team.time, "DD-MM-YYYY HH:mm:ss").fromNow();
            mysqlQ("SELECT I.ID AS InatID, J.Name AS JobTitle, C.Name AS Category FROM `Jobs` AS J JOIN Jobs2INAT AS J2I ON J.ID = J2I.JobsID JOIN INAT AS I ON I.ID = J2I.INATID JOIN categories AS C on C.ID = J.Categories WHERE I.ID = " + team.ID).then(function (jobs) {
                    team.jobs = jobs;
                });
        });
        var context = {
            INAT: INAT
        };
        var homeTemplate = pug.compileFile(__dirname + '/templates/home.pug');
        var html = homeTemplate(context);
        res.send(html);
    });
})

Possible duplicate of [How do I return the response from an asynchronous call?](http://stackoverflow.com/questions/14220321/how-do-i-return-the-response-from-an-asynchronous-call) - while this code is NodeJS and not AJAX, the same principle applies — Sᴀᴍ Onᴇᴌᴀ, May 16 '17 at 19:36

score 1 · Accepted Answer · edited May 17 '17 at 09:52

You actually need to create a second promise that will resolve once all your secondary queries have returned, building a second promise with when.all() or bluebird.all (or their equivalent *.map)

You are also trying to modify the entries in your INAT object but to do so you too need to wait for your secondaries promise to resolve before updating INAT and sending your final result.

There are many way to do that but something like this should work

var Promise = require('bluebird'); // or use any other Promise library

admin.get('/', function (req, res) {
    mysqlQ("SELECT I.ID as ID, C.ID AS CID, I.PD AS PostDate, C.Name AS CatName, U.UserName AS UserName, I.Title AS Title, I.Post AS PostData FROM categories AS C JOIN INAT AS I ON C.ID = I.Category JOIN Users AS U on U.ID = I.User")
        .then(function (INAT) {
            var promiseArray = [];
            promiseArray.push(Promise.resolve(INAT));
                // For each INAT entry create a new Promise that will resolve with your job
                INAT.forEach(function (team) {
                    team.time = moment(team.PostDate).format("DD-MM-YYYY HH:mm:ss");
                    team.TS = moment(team.time, "DD-MM-YYYY HH:mm:ss").fromNow();
                    promiseArray.push(
                        mysqlQ("SELECT I.ID AS InatID, J.Name AS JobTitle, C.Name AS Category FROM `Jobs` AS J JOIN Jobs2INAT AS J2I ON J.ID = J2I.JobsID JOIN INAT AS I ON I.ID = J2I.INATID JOIN categories AS C on C.ID = J.Categories WHERE I.ID = " + team.ID)
                    );
                });

            return Promise.all(promiseArray);
        }).then(function(results) {
            // Add the looked-up jobs to INAT
            var [INAT, ...jobs] = results;
            INAT.forEach(function(team, i) {
                team.jobs = jobs[i];
            })

            // Send your answer
            var context = {
                        INAT: INAT
            };
            var homeTemplate = pug.compileFile(__dirname + '/templates/home.pug');
            var html = homeTemplate(context);
            return res.send(html);
        }).catch(function(err) {
            // do some error handling
            return res.send("Unable to process / => err = "+err);
        })
    })

PS : don't forget to end all your .then(function() {}) with a return statement

Thanks that got it, and really helped me understand promises! — bertie.io, May 17 '17 at 09:24
@bertie.io I have applied your edit, that according to me was incorrectly declined by the edit review queue, if Boris like to rollback he is free to do so. — Petter Friberg, May 17 '17 at 09:53

taskforce · Answer 2 · 2017-05-16T20:41:59.937

Your problem is this, you are trying to assign your variable before the 2nd promise resolves.

If you are waiting for 2 promises to return, the final action needs to be executed after the last promise resolves

 admin.get('/', function (req, res) {
    mysqlQ("SELECT I.ID as ID, C.ID AS CID, I.PD AS PostDate, C.Name AS CatName, U.UserName AS UserName, I.Title AS Title, I.Post AS PostData FROM categories AS C JOIN INAT AS I ON C.ID = I.Category JOIN Users AS U on U.ID = I.User").then(function (INAT) {
    INAT.forEach(function (team) {
        team.time = moment(team.PostDate).format("DD-MM-YYYY HH:mm:ss");
        team.TS = moment(team.time, "DD-MM-YYYY HH:mm:ss").fromNow();
        mysqlQ("SELECT I.ID AS InatID, J.Name AS JobTitle, C.Name AS Category FROM `Jobs` AS J JOIN Jobs2INAT AS J2I ON J.ID = J2I.JobsID JOIN INAT AS I ON I.ID = J2I.INATID JOIN categories AS C on C.ID = J.Categories WHERE I.ID = " + team.ID).then(function (jobs) {
                team.jobs = jobs;

                //  *************<<<<<<<<<<<
                //  Any code that needs to wait for the second mysqlQ query needs to go here
                //  *************<<<<<<<<<<<
              var context = {
                  INAT: INAT
              };
              var homeTemplate = pug.compileFile(__dirname + '/templates/home.pug');
              var html = homeTemplate(context);


            });
    });
    res.send(html);
    // var context = {
    //     INAT: INAT
    // };
    // var homeTemplate = pug.compileFile(__dirname + '/templates/home.pug');
    // var html = homeTemplate(context);
    // res.send(html);
});

})

Something like the above would work, although still a bit hacky, and is probably frowned upon due to the "callback/promise hell" that JS developers are fond of.

That would cause the Express request to be send each time the foreach loop runs sending a request to the browser each time — bertie.io, May 16 '17 at 19:51
@bertie.io you're right! edited my post to fix that. cannot send headers twice. — taskforce, May 16 '17 at 20:42

NodeJS running a promise-mysql query inside a foreach loop waiting for result each time

2 Answers2