Modify results from Nested Queries in NodeJS

Question

Naive to NodeJS and trying to figure out a way to add results from second table to result set of first table in node js.

How can I access results from first query inside the second query?

Following is my code snippet with comments

function getTasks(callback) {
//first query gives result set
connection.query('SELECT * FROM ' + properties.get('database.Table') +' order by timestamp desc', function(err, rows){
    if(!err){
        //for each result from result set, match on Id and get values from table_2
        for (var i = rows.length - 1; i >= 0; i--) {
            connection.query('SELECT * FROM table_2 where taskId = "' + rows[i].taskId + '"', function(err, sets){
                if(!err){
                    //if we have any results from table_2 then create an object
                    if(sets.length > 0){
                        var setStatus = [];
                        for (var i = sets.length - 1; i >= 0; i--) {
                            setStatus[i] = {Status : sets[i].type+'-'+sets[i].status};
                        }
                        //add the setStaus object to results from first table (to rows)
                        //ISSUE: accessing rows[i] here is alwyas undefined??
                    }
                }
            });
        }
        //need to send the rows with updates from nested block
        callback(rows);
    }
});

UPDATE: async/await solution worked and by changing i to j for inner iterator!

use async/await for async call.

Rahul Sharma
– Rahul Sharma

2018-02-20 17:11:47 +00:00
Commented Feb 20, 2018 at 17:11 — Rahul Sharma
– Rahul Sharma, Commented Feb 20, 2018 at 17:11

Rahul Sharma · Accepted Answer · 2018-02-20 17:21:44Z

You can handle using async/await

const execute = (query) => {
    return new Promise((resove, reject) => {
        connection.query(query,
            function (err, rows) {
                if (err) reject(err);
                resove(rows);
            });
    });
}

const getTasks = async () => {
    const query = 'SELECT * FROM ' + properties.get('database.Table') + ' order by timestamp desc';
    const rows = await execute(query);
    for (var i = rows.length - 1; i >= 0; i--) {
        const innerQuery = 'SELECT * FROM table_2 where taskId = "' + rows[i].taskId + '"';
        const sets = await execute(innerQuery);
        //Do some stuff
        if (sets.length > 0) {
            var setStatus = [];
            for (var i = sets.length - 1; i >= 0; i--) {
                setStatus[i] = {
                    Status: sets[i].type + '-' + sets[i].status
                };
            }
        }
    }
    return rows;
};

Yau can call either inside await or given below

getTasks().then((rows) => {
    console.log(rows);
}).catch((err) => {
    console.log(err);
})

displaynamevalue · Accepted Answer · 2018-02-20 17:21:18Z

1

The declaration of i in the second for loop overwrites the value of i from the first loop.

You can fix this by changing the variable declared as part of the second loop to something other than i.

answered Feb 20, 2018 at 17:21

displaynamevalue

1384 bronze badges

Comments

Eric Ly · Accepted Answer · 2018-02-20 17:22:03Z

1

Try to use let or const instead of var. Use a different variable name than i for all your iterators. This is probably messing up with your first i, thus leading to rows[i] as undefined.

You also probably want to look for a MySQL node module that supports ES6 Promises like this one : https://www.npmjs.com/package/mysql2

answered Feb 20, 2018 at 17:22

Eric Ly

2,1231 gold badge22 silver badges27 bronze badges

Collectives™ on Stack Overflow

Modify results from Nested Queries in NodeJS

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related