Nested queries in MYSQL and nodejs Async Issue

Krishnan

New Member
#1
I want to execute an sql query for every row of another query so i wrote a function like
Mã:
export function getLocations(req, res) {
let appData = [];
const database = new Database();
database.query('select * from districts')
    .then(rows => {
        rows.forEach(row => {
            const new_database = new Database();
            new_database.query(`SELECT locations.id,locations.name
            FROM locations
            .....
            WHERE locations.district=?`, row.id)
                .then(sub_rows => {
                    let district = row;
                    district["locations"] = sub_rows;
                    appData.push(district);
                    new_database.close();
                }, err => {
                    return new_database.close().then(() => { throw err; })
                })
                .catch(err => {
                    console.log(err);
                    res.status(500).json("Database Error");
                })
    }, err => {
        return database.close().then(() => { throw err; })
    }).then(result => {
        //final result
        res.status(200).json(appData);
        database.close()
    })
    .catch(err => {
        console.log(err);
        res.status(500).json("Database Error");
    })
}
and my database class is
Mã:
const mysql = require('mysql');

class Database {
constructor() {
    this.connection = mysql.createConnection({
        host: process.env.DB_HOST,
        user: process.env.DB_USERNAME,
        password: process.env.DB_PASSWORD,
        database: process.env.DB_NAME,
        port: 3306,
        debug: false,
        multipleStatements: false
    });
}
query(sql, args) {
    return new Promise((resolve, reject) => {
        this.connection.query(sql, args, (err, rows) => {
            if (err)
                return reject(err);
            resolve(rows);
        });
    });
}
close() {
    return new Promise((resolve, reject) => {
        this.connection.end(err => {
            if (err)
                return reject(err);
            resolve();
        });
    });
}
}
module.exports = Database;
This however on execution goes on and print the final result (empty array) without waiting to execute the sub queries. I tried async await on row and new_database.query but still it doesn't wait for subquery execution to finish. What can I do here?
 

Admin

Administrator
Thành viên BQT
#2
I have improved your logic for sub-queries and re-wrote them using Promise.all():
Mã:
export function getLocations(req, res) {
    let appData = [];
    const database = new Database();
    database.query('select * from districts')
        .then(rows => {

            // create promise list for all sub-queries
            const promise_list = rows.map(row => new Promise((resolve, reject) => {
                const new_database = new Database();
                return new_database.query(`SELECT locations.id,locations.name FROM locations .... WHERE locations.district=?`, row.id)
                    .then(sub_rows => {
                        let district = row;
                        district["locations"] = sub_rows;
                        appData.push(district);
                        new_database.close();
                        resolve(district);
                    }, err => {
                        return new_database.close().then(() => { throw err; })
                    });
            })
            );

            // execute all sub-queries in parallel and wait for them
            Promise.all(promise_list).then(result => {
                res.status(200).json(appData);
                database.close()
            }).catch(err => {
                console.log(err);
                res.status(500).json("Database Error");
            });

        });
}
PS: You can declare appData as const, you're just pushing data into it. Not re-assigning it.
 
OP
OP
K

Krishnan

New Member
#3
i logged and now the execution is in correct order but is not returning and stuck. the final result is not printed and is always waiting.
 

Admin

Administrator
Thành viên BQT
#4
@Krishnan remove return from line return new_database.close(); and addresolve(district) after that. I am also editing the answer to make it more clear. Unfortunately I can't test it.
 

Từ khóa phổ biến

You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.

Chủ Đề Xem Nhiều

Top