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