Alan Alan - 3 months ago 8
Javascript Question

Avoid using for loop to get total of a query in Node.js

I have a structure that looks like this:

[{id:1, amount:2},{id:3, amount:4}]


'Id' represents the Id's of a product and amount how many the order has. So I want to get the price of each product and add it to a variable (Example: total = (product1.price * amount1) + (product2.price * amount2) + ... But I don't know how to do it without a for loop and I've had issues doing queries in a for loop when using node.js. The price row is stored in a table named "product" .

So is there a way to avoid it..? Query looks like this without the for loop:

pool.getConnection(function(err, connection) {

if(err) {
console.log(err);
callback(true);
return;
}
var total = 0;
//for loop with query to add each product*quantity to total ?
connection.query("SELECT price FROM product WHERE id_product = ? ", [id_product], function(err, results) {
connection.release();
if(err){
console.log(err);
callback(true);
return;
}
... // Do something
});


});`

Forgot to mention the array may be bigger or just contain 1 element.

Answer

The query part:

I assume you want to avoid issuing multiple queries instead of one batch SELECT. To optimize this, you can either fetch all products with their prices, or use a id_product IN (ID1, ID2, ID3, ...) clause.

To gather all IDs into a single array, you can use Array.prototype.map:

var idsArray = myArray.map((myObj) => myObj.id));

Assuming you use mysql driver, your batch select would probably look like this:

connection.query(
    "SELECT id_product, price FROM product WHERE id_product IN (?)",
    [ idsArray ],
    callback
);

The JS loop part:

In general, there is no way you could avoid using "loop" in this task. There are things like reduce, some libraries probably provide sophisticated sum functions, but their implementations rely solely on loops.

But there is no need to avoid loops. When combined with batch select presented in "The query part", the best way to sum your prices is to use Array.prototype.reduce:

var total = myArray.reduce((prev, myProduct) => {
    // Assuming you've put query result inside "prices" object with ID mapping
    return prev + (myProduct.amount * prices[myProduct.id]);
}, 0);

Note: my examples use Arrow functions syntax, so make sure you have the latest Node with ES2015+ support. You can also switch back to anonymous functions if you like.