Sequelize Query Execution In Loop

Question

Below iam calling addUpdateDailyLeads with an array like

[{
    "yyyymmdd": "20191124",
    "admin_login":"rasheed.s",
    "category":"PO",
    "amount":10,
    "office_id":10000,
    "new_leads_attempted":10
},
{
    "yyyymmdd": "20191124",
    "admin_login":"rasheed.s",
    "category":"PO",
    "amount":10,
    "office_id":10000,
    "new_leads_attempted":10
},
{
    "yyyymmdd": "20191125",
    "admin_login":"prajeed.av",
    "category":"FHL",
    "amount":10,
    "office_id":10000,
    "new_leads_attempted":10
}
]

So,key 0 should insert, key 1 should update because duplicate key constratint, key 2 will insert,

but im getting duplicate key constraint error on key 1,because array map not waiting for the query to be executed .

const addUpdateDailyLeads = async (req, res) => {
  let admin_login,category,office_id,new_leads_attempted,yyyymmdd,where,values;
  let data = req.body;

  req.body.map(async function(item,i){
    admin_login = item.admin_login,
    category = item.category,
    office_id = item.office_id,
    new_leads_attempted = item.new_leads_attempted,
    yyyymmdd    = item.yyyymmdd;
    where = {yyyymmdd:yyyymmdd, admin_login:admin_login, category:category};
    values = {yyyymmdd:yyyymmdd, admin_login:admin_login, category:category,office_id:office_id,new_leads_attempted:new_leads_attempted,update_date:moment().format('YYYYMMDDHHmmss')};
    console.log("calling  ",i);
    let chck = await addUpdateDailyLeadsCollection({where:where,values:values})
    console.log("")
    console.log("called")
  })

  res.json({ code: '200', message: `Advisor Daily Leads Updated ${admin_login}` });

}


const addUpdateDailyLeadsCollection = async data => {
  let transaction;    
  let where = data.where
  let values = data.values
  var Sequelize = require("sequelize");
  console.log("startef 1");
  await AdvisorLeads.findOne({ where: where }, { useMaster: true }).then( async(data)=>{
    console.log("waited");
    if(data){
          await data.update({new_leads_attempted: Sequelize.literal('new_leads_attempted + '+values.new_leads_attempted)}).then(data=>{
           console.log("updated")
           return Promise.resolve(1);
         })
    }else{
        AdvisorLeads.create(values).then(data=>{
           console.log("inserted")
           return Promise.resolve(1);
        })
    }
  })


};

final output on console

calling   0
startef 1
waiting 1
calling   1
startef 1
waiting 1
calling   2
startef 1
waiting 1
waited
waited
waited
called

called

called

inserted
inserted

My expected output like
calling   0
startef 1
waiting 1
waited
inserted
called

calling   1
startef 1
waiting 1
waited 
updated
called

calling   2
startef 1
waiting 1
waited
inserted
called

Finally whait i need is to wait for each item ,execute all queries and then process next item

KenOn10 · Answer 1 · 2020-01-27T18:22:12.687

I think you can solve by using await on the update and create statements....

But also take a look at the UPSERT method, which could simplify your code quite a bit. From The Sequelize API Reference: "Insert or update a single row. An update will be executed if a row which matches the supplied values on either the primary key or a unique key is found."

Addendum: for synchronizing async/await, there are many ways to do this, as detailed in this post. Here's some code I set up following the ES7 method:

let params = [{id : 1, sal : 10}, {id : 44, sal: 30}, {id : 1, sal : 20}];

async function doUpsertArrayInSequence(myParams) {

    let results = [];
    for (let i = 0; i < myParams.length; i++) {
        let x = await User.findByPk(myParams[i].id).then(async (u) => {
            if (u != null) {
                await u.update({ sal : u.sal + myParams[i].sal});
            } else {
                await User.create({id: myParams[i].id, sal: myParams[i].sal});                    
            }
        });
        results.push(x);
    }
    return results;
}
await doUpsertArrayInSequence(params).then(function(result) {
    User.findAll().then(proj => {
        res.send(proj);
        next();
    });    
})

From the log, I can see a) a SELECT, followed by an UPDATE or INSERT for each row (in sequence). b) the 2nd occurrence of id=1 reflects the update of the 1st occurrence. c) the final findAll reflects all inserts and updates.

HTH

Qusetion updated clearly with await for create and update,but that didnt work.I know we can acheive by it using upsert method.But i need to learn clear async and acit with sequlize. — rasheed sk, Jan 27 '20 at 15:57

Sequelize Query Execution In Loop

1 Answers1