0

The task is to get parts of data from the columns object and send it to the database. The parts must have the maximum size of insertMaxRows.

I tried some iterations and using the modulo operator. However I am still a beginner, can't get my head around this problem.

var insertMaxRows = utils.GetInsertMaxRows();   //right now its 2
var idx = 1;

for (var i = 0; i < columns.id.length; i++) {

if (idx % insertMaxRows == 0){

    var params = [];

    //this takes just 1 value, i know
    params.push(columns.codes[idx -1]);     
    params.push(columns.product_id[idx -1]);
    params.push(columns.id[idx -1]);

    updateSql = "UPDATE TProductCodes SET code =?, product_id =? WHERE id =?";

    sql.query(connection, params, etc, callback) {

        //sends stuff to database
    });
idx++;
}

When insertMaxRows is 2, for example, this code should send the first 2 items in the arrays to the database, then the next 2, and so on, then the rest is there is any.

Please note that this is for NODE JS and will be sent to the database in a asynchronous function, if it matters at all.

Cristi Priciu
  • 59
  • 9
  • Possible duplicate of [Split array into chunks](https://stackoverflow.com/questions/8495687/split-array-into-chunks) – VLAZ Jun 11 '19 at 11:07
  • use `array.shift()` this returns the first item from array and removes it in one step – 2x2p Jun 11 '19 at 11:12
  • I've posted an answer but I have to say that i'm bit confused on what you want. My answer will achieve what you asked for, but it doesn't makes sense for me... – DontVoteMeDown Jun 11 '19 at 11:19
  • 1
    @DontVoteMeDown i think you understand this all right. Thank you! I just need some time to adapt and test this, BRB. The code is for updating a table of product codes in a SQL Server database. – Cristi Priciu Jun 11 '19 at 11:26

2 Answers2

1

Here's how I might approach it. It iterates over the data and creates a new Promise containing an UPDATE call for each data chunk (which I splice off the array). You can use Promise.all to wait until all the updates have resolved.

// set up the data
const data = [];
for (let i = 0; i < 100; i++) {
  data.push(i);
}

function UPDATE(data) {
  return new Promise(resolve => {

    // mimic an SQL UPDATE query
    setTimeout(() => resolve(), 2000);
  });
}

function getPromises(data, limit) {
  const promises = [];

  // Iterate through the data, splicing off a chunk at
  // a time, and creating a new promise for each
  for (let i = 0; i < data.length; i++) {
    const chunk = data.splice(0, limit);
    promises.push(UPDATE(chunk));
  }
  return promises;
}

(async () => {
  const limit = 12;

  // Wait until all the promises have resolved
  await Promise.all(getPromises(data, limit));
  console.log('Done');
})();
Andy
  • 61,948
  • 13
  • 68
  • 95
0

An other approach would be to prepare your linear arrays in sub groups like triads or pairs based on the limitation value of insertMaxRows in your case it's 2 so the arrays will be split in pairs.

Thereafter you can do your forEach loop on the pairs. In the example there are 7 values so the last value will be single.

After processing your columns object will look like this:

{ 
  id: [[1, 2], [3, 4], [5, 6], [7]], 
  product_id: [[11, 22], [33, 44], [55, 66], [77]], 
  codes: [["a", "b"], ["c", "d"], ["e", "f"], ["g"]]
}

An example of the function to subdivide 3 linear arrays inside the columns object by a limit value.

var insertMaxRows = 2;

var columns = {
  id : [1,2,3,4,5,6,7],
  codes : ['a','b','c','d','e','f','g'],
  product_id : [11,22,33,44,55,66,77],
};

console.log(columns); // before grouping values

function groupArrayByLimit(arr,limit){
    if(typeof arr == 'object' && typeof limit == 'number' && limit>=1){
      var res = [];
      while (arr.length) {        
        var tmp = [];
        var itt = 0
        while (itt<limit && arr.length){
          tmp.push(arr.shift());
          itt++;
          //console.log(tmp);
        }
        res.push(tmp);
        //console.log(res);
      }
    return res;
    }
  return null;
}

columns.id = groupArrayByLimit(columns.id,insertMaxRows);
columns.codes = groupArrayByLimit(columns.codes,insertMaxRows);
columns.product_id = groupArrayByLimit(columns.product_id,insertMaxRows);

console.log(columns);  // after regrouping values

If you need more help on how to stitch the pairs or triads back into a linear array like for example this style [ 1, 11, "a", 2, 22, "b" ] as parameters to your MySQL query just let us know in a comment.

2x2p
  • 414
  • 3
  • 17