SQL query with async each

Question

My problem is when I am getting array of users and trying to run async each it start each sqlRequest and not going one by one pushing in array.

It should do sqlRequest 1 by 1, and not do all by each user sqlRequest and the push in an array.

Here is a async each

function getUserFavCat(params, callback) {
  var usersArrayCat = [];
  async.each(params, function (user, cb) {
    sqlRequest("SELECT b_cat.title, b_cat.id FROM dbo.Students st INNER JOIN dbo.SaleView sv ON sv.userId = st.id INNER JOIN dbo.KEY_BrandcategoryToSale b_key ON b_key.saleId = sv.saleId INNER JOIN dbo.BrandCategories b_cat ON b_cat.id = b_key.brandCategoryId WHERE st.id = " + user.id, function (err, result) {
      if (!result) {
        //console.error("NO FAVOURITE CATEGORY FOR USER " + JSON.stringify(user))
      } else if (result.length == 0) {
        //console.error("NO FAVOURITE CATEGORY FOR USER " + JSON.stringify(user))
      } else {
        user.favouriteCat = utils.takeMostRepeatingObj(result);
        usersArrayCat.push(user);
      }
      cb();
    })
  }, function() {
    callback(null, usersArrayCat)
  });
};

Here is the SQL query:

function sqlRequest (sqlQuery, callback) {
  var connection = new sql.Connection(sql_conf, function (err) {
    if (err){
      console.log(err)
    } else {
      var request = new sql.Request(connection);
      request.query(sqlQuery, function(err, result) {
        console.log(result)
        if(err){
          console.error(err)
        } else if(!result){
          console.error("NO RESPONSE SQL QUERY")
        } else {
          callback(null, result);
          connection.close();
        }
      })
    }
  });
  connection.on('error', function(err) {
    console.log(err);
  });
};

Not related to your question, but don't built your queries like that - you're opnening a hole to SQL Injection. — Evaldas Buinauskas
– Evaldas Buinauskas, Commented Feb 26, 2017 at 11:17
Sorry, I've meant this part WHERE st.id = " + user.id. Don't concatenate your strings. Whatever library you use, it should let you build parameterized queries, do that. — Evaldas Buinauskas
– Evaldas Buinauskas, Commented Feb 26, 2017 at 11:27

parwatcodes · Accepted Answer · 2017-02-26 17:07:43Z

use async.eachLimit to limit the no. of request

function getUserFavCat(params, callback) {
    var usersArrayCat = [];
    console.log(`length of array ${params.length}`)
                        // 1 here means 1 request at a time
    async.eachLimit(params, 1, function (user, cb) {
        sqlRequest("SELECT b_cat.title, b_cat.id FROM dbo.Students st INNER JOIN dbo.SaleView sv ON sv.userId = st.id INNER JOIN dbo.KEY_BrandcategoryToSale b_key ON b_key.saleId = sv.saleId INNER JOIN dbo.BrandCategories b_cat ON b_cat.id = b_key.brandCategoryId WHERE st.id = " + user.id, function (err, result) {
            if (!result) {
                //console.error("NO FAVOURITE CATEGORY FOR USER " + JSON.stringify(user))
            } else if (result.length == 0) {
                //console.error("NO FAVOURITE CATEGORY FOR USER " + JSON.stringify(user))
            } else {
                user.favouriteCat = utils.takeMostRepeatingObj(result);
                usersArrayCat.push(user);
                cb();
            }
        })
    }, function (err) {
        if (err) return callback(err);
        callback(null, usersArrayCat)
    });
};

digit · Accepted Answer · 2017-02-26 11:35:02Z

Try this trick by using async.map and push for every callback to an array. Then you can use async.parallel to filter out the callback results for the success user and push it to usersArrayCat arrays

function getUserFavCat(params, callback) {
  var usersArrayCat = [], callbackArrays = [];
  async.map(params, function (user, cb) {
    callbackArrays.push(function (cb) {
       sqlRequest("SELECT b_cat.title, b_cat.id FROM dbo.Students st INNER JOIN dbo.SaleView sv ON sv.userId = st.id INNER JOIN dbo.KEY_BrandcategoryToSale b_key ON b_key.saleId = sv.saleId INNER JOIN dbo.BrandCategories b_cat ON b_cat.id = b_key.brandCategoryId WHERE st.id = " + user.id, function (err, result) {
       if (!result) {
         user.status = '99'; // error status
         cb(null, user);
       } else if (result.length == 0) {
         user.status = '99'; // error status
         cb(null, user);
       } else {
         user.favouriteCat = utils.takeMostRepeatingObj(result);
         user.status = '00'; //success status
         cb(null, user);
       }
     });
   }, function(err, results) {
     // comes here after all individual async calls have completed
     // check errors; array of results is in data
     cb(null, results)
   });

   async.parallel(callbackArrays, function (err, results) {
      results.forEach (function (elem, ind) {
        if (elem.status == '00') {
           usersArrayCat.push(elem);
        }
      });
   });
 };

No. It did not help. Problem is that it don't go step by step, it go to callback only after each user proceed query

Collectives™ on Stack Overflow

SQL query with async each

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related