CODEWITHSUNDEEP
node.jsasynchronousnode-mysql
Ram Alsaifi
Ram Alsaifi

Reputation: 23

Runing nested mysql queries in node.js

I'm trying to construct an array of objects and render them using ejs. The objects array is coming from the database from a table called products. I was able to run a query and get the products data but I need to run a nested query on each product using the category id of the products to get other info like category name (in another table). I tried using a foreach loop on every product and run a nested query but the problem is since the nested queries in foreach loop is done asynchronously, I can't serve the data from outside the foreach.

Is there a way to run multiple queries and save the data to a variable and serve that variable from outside the query callback?

part of my code

database.con.query('SELECT * FROM products', (err, products) => {
if(err) throw err;
let productsArr = products; // productsArr will hold all data to be rendered
productsArr.forEach((product, index) => {

  database.con.query(`SELECT * FROM category WHERE Id = ${product.Category_id}`, (err, category) => {
    if(err) throw err;
    productsArr[index].cat = category[0]; // Save the categories of each product as a property
  }); // End of nested query

});// End of foreach

res.render('products/index.ejs', {products: productsArr}); // 'productsArr' have all products data but not the categories (cat propery) of each product
});

Note: I could run a joined query to get all the data in one go but I need to know how to fix it using the above method for other reasons

I hope I'm making sense with my broken English. Any help is appreciated

Upvotes: 0

Views: 303

Answers (1)

Marc
Marc

Reputation: 3874

I thought of somehting like this: (Note: not tested)

database.con.query('SELECT * FROM products', (err, products) => {

    if (err) throw err;

    let wrapper = products.map((product) => {
        return new Promise((resolve, reject) => {

            database.con.query(`SELECT * FROM category WHERE Id = ${product.Category_id}`, (err, category) => {
                if (err) {

                    reject(err);

                } else {

                    resolve(category[0]);

                };
            });

        });
    });


    Promise.all(all).then((results) => {

        let arr = products.map((product, index) => {
            product.cat = results[index];
            return product;
        });

        res.render('products/index.ejs', {
            products: arr
        });

    }).catch((err) => {

        console.error(err);

    });



});

Upvotes: 1

Related Questions