Runing nested mysql queries in node.js

Question

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

One possible way: Promise.all() Some code from you would be helpfull — Marc
– Marc, Commented Nov 24, 2020 at 11:42
I tried using array.map instead of foreach and return a promise for each query then, resolved all promises using Promise.all(). Though the whole thing looks messy now but that have worked. Thank you @Marc — Ram Alsaifi
– Ram Alsaifi, Commented Nov 24, 2020 at 12:54
Thats exacty what i put for you together right now. should i post my answer or are you good ? — Marc
– Marc, Commented Nov 24, 2020 at 12:58

Marc · Accepted Answer · 2020-11-24 13:47:42Z

1

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);

    });



});

edited Nov 24, 2020 at 13:47

answered Nov 24, 2020 at 13:01

Marc

4,0493 gold badges21 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ram Alsaifi Over a year ago

That is what I tried based on your comment and it worked. One thing though, wrapper will be an array of promises so we need Promise.all(wrapper).then(whatever) to handle them which is what you mentioned in your comment. Thank you for your help @Marc

Marc Over a year ago

Yeah, your right. In the heat of the moment and without testing, this kind of mistakes happen ;) Fixed it.

Collectives™ on Stack Overflow

Runing nested mysql queries in node.js

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest