sequential queries from database using sequalize in node+express

Question

I am just starting node and am from PHP background. I have been trying to figure out promisses for some time now but unable to figure it out.

I am trying to use result of one query in 2nd query in a loop, but having TWO issues:

-ONE: typeof value shows an object; but when I console it out, it shows undefined

-TWO: my results are not getting in the right order. I am trying to use 'then()' but not able to quite figure it out. "inside promise 2" is consoled out before the loop in 'then()' 1 block

Here's what I'm doing in code:

exports.followers_temp = function(req, res)
{
    db.sequelize.query('SELECT DISTINCT public."Users".id as user_id, public."Users".full_name, public."Users".profile_image_url, (SELECT COUNT(*) FROM public."Followships" WHERE public."Followships".leader_id = public."Users".id AND public."Followships".follower_id = :leader_id) AS is_followed FROM public."Users"  INNER JOIN public."Followships" ON public."Users".id = public."Followships".follower_id WHERE  public."Followships".leader_id = :leader_id LIMIT :limit OFFSET :offset',  { replacements: { leader_id: req.params.leader_id, limit: req.params.limit, offset: req.params.offset }, type: db.sequelize.QueryTypes.SELECT}).
    then(function(data){
      console.log('i am here; length: ' + data.length);
        return new Promise(function(resolve, reject) {
              console.log('inside promise 1, should have access to data object');

              console.log('before loop');
              for(let i=0; i < data.length; i++){
                  var temp = db.sequelize.query('(SELECT COUNT(*) AS is_following FROM public."Followships" WHERE public."Followships".leader_id = ' + data[i].user_id + ' AND public."Followships".follower_id = :my_id)',  { replacements: { leader_id: req.params.leader_id, my_id: req.params.my_id, limit: req.params.limit, offset: req.params.offset }, type: db.sequelize.QueryTypes.SELECT})
                  temp.then(function(value){

                    // Issue ONE
                      console.log('type: '+ typeof value); // it's an object
                      console.log('is_following: '+ value.is_following); // yet it's giving undefined

                      console.log('value: '+ JSON.stringify(value)); // but it prints out when I stringify it

                      data[i].is_followed = value.is_following;
                  });
              }
            resolve(data);
            });
    })

    .then(function(my_array){
        // Issue TWO
        console.log('inside promise 2');

        return res.status(200).send({
            error:false,
            message: my_array
        });
    })
        .catch(function(err) {
            console.log('inside promise catch');
            return res.status(400).send({
                error:true,
                message: errorHandler.getErrorMessage(err)
            });
        });
};

and here is the console output:

i am here; length: 5          
inside promise 1, should have 
before loop                   
inside promise 2              
type: object                  
is_following: undefined       
value: [{"is_following":"1"}] 
type: object                  
is_following: undefined       
value: [{"is_following":"0"}] 
type: object                  
is_following: undefined       
value: [{"is_following":"1"}] 
type: object                  
is_following: undefined       
value: [{"is_following":"1"}] 
type: object                  
is_following: undefined       
value: [{"is_following":"0"}]

Please let me know -what am I doing wrong? -if you could working of promises syntax wise... would be great

Thanking you in anticipation; Please ignore rookie mistakes as I am a rookie as of now.

loops and promises do not mix well. Instead use `Promise.all` — ViKiG, Feb 08 '18 at 13:56
hmm... Promise.all --- that's a new term for me... i'll look into it's functionality and try to use it if I understood it well. thank you for the tip. — Muhammad Saqib Bilal, Feb 08 '18 at 13:59

score 0 · Answer 1 · answered Feb 08 '18 at 14:09

db.sequelize.query is an asynchronous call put in a for loop. This will start queries but have not successfully completed yet. So, your data is not complete yet and you are resolving it already. With Promise.all you can write it as follows:

Promise.all(data.map((val) => { // here val = data[i] inside the query
    return new Promise((resolve, reject) => {
        db.sequelize.query(...)
            .then((value) => {
                // modify your data object here.
                resolve();
            });
    });
})).then(() => { resolve(data);});

HMR · Answer 2 · 2018-02-09T09:42:27.827

You do not need to use new Promise if you already have a promise:

exports.followers_temp = function (req, res) {
  db.sequelize.query('SELECT DISTINCT public."Users".id as user_id, public."Users".full_name, public."Users".profile_image_url, (SELECT COUNT(*) FROM public."Followships" WHERE public."Followships".leader_id = public."Users".id AND public."Followships".follower_id = :leader_id) AS is_followed FROM public."Users"  INNER JOIN public."Followships" ON public."Users".id = public."Followships".follower_id WHERE  public."Followships".leader_id = :leader_id LIMIT :limit OFFSET :offset', { replacements: { leader_id: req.params.leader_id, limit: req.params.limit, offset: req.params.offset }, type: db.sequelize.QueryTypes.SELECT })
  .then(function (users) {
    console.log('i am here; length: ' + users.length);
    //do not use new Promise if you already have a promie
    //https://stackoverflow.com/a/48576246/1641941
    //use promise.all and map data to promises
    return Promise.all(
      users.map(
        user=>{
          db.sequelize.query(
            '(SELECT COUNT(*) AS is_following FROM public."Followships" WHERE public."Followships".leader_id = :leader_id AND public."Followships".follower_id = :my_id)', 
            { replacements: 
              { leader_id: user.user_id,//changed leader_id
                my_id: req.params.my_id//removed limit and offset 
              },
              type: db.sequelize.QueryTypes.SELECT 
            })
            .then(function (value) {
              console.log('type: ' + typeof value);
              //value is an array
              console.log('is_following: ' + value[0].is_following);
              console.log('value: ' + JSON.stringify(value));    
              user.is_followed = value[0].is_following;
              return user;
            });
        }
      )
    )
  })
  .then(function (users) {
    // Issue TWO
    console.log('inside promise 2',users);
    return res.status(200).send({
      error: false,
      message: users
    });
  })
  .catch(function (err) {
    console.log('inside promise catch',err);
    return res.status(400).send({
      error: true,
      message: errorHandler.getErrorMessage(err)
    });
  });
};

Thank you for the help... I see a good coding style,wil follow it but your code is still printing "inside promise 2" before printing other data i.e: "i am here; length: 5 inside promise 2 [ undefined, undefined, undefined, undefined, undefined ] type: object is_following: 0 value: [{"is_following":"0"}] type: object is_following: 0 value: [{"is_following":"0"}] type: object is_following: 0 value: [{"is_following":"0"}] ... and how did you know it was an array while typeof is showing object? — Muhammad Saqib Bilal, Feb 09 '18 at 07:46
I think you made a mistake in the second query where you replace leader_id with request.params.leader_id, will update the anser. I know it's an object because array is object (typeof [] is "object")your question shows it is: `value: [{"is_following":"1"}] ` an object is `{}` an array is `[]` an object in an array is: `[{}]` — HMR, Feb 09 '18 at 09:35
o yes, array is an object in javascript... in second query leader_id is not even used, it's redundant. I removed last then() and tried to send response at the end of promise.all but it says 'header already sent'... maybe because of the loop. I am still trying to fix it and will post here when fixed... — Muhammad Saqib Bilal, Feb 09 '18 at 10:32

Muhammad Saqib Bilal · Accepted Answer · 2018-02-14T08:18:15.537

After searching stackoverflow in more depth, I was able to solve my problem. I used help provided on THIS POST.

I thank stackoverflow community for all the help. I will try to contribute as well in future.

Final solution looks like this:

    exports.followers_temp = function (req, res) {
    db.sequelize.query('SELECT DISTINCT public."Users".id as user_id, public."Users".full_name, public."Users".profile_image_url, (SELECT COUNT(*) FROM public."Followships" WHERE public."Followships".leader_id = public."Users".id AND public."Followships".follower_id = :leader_id) AS is_followed FROM public."Users"  INNER JOIN public."Followships" ON public."Users".id = public."Followships".follower_id WHERE  public."Followships".leader_id = :leader_id LIMIT :limit OFFSET :offset', { replacements: { leader_id: req.params.leader_id, limit: req.params.limit, offset: req.params.offset }, type: db.sequelize.QueryTypes.SELECT })
        .then(function (users) {
            var promises = users.map(function(user){
                return db.sequelize.query(
                    '(SELECT COUNT(*) AS is_following FROM public."Followships" WHERE public."Followships".leader_id = ' +
                    user.user_id + ' AND public."Followships".follower_id = :my_id)',
                    { replacements:
                            { leader_id: req.params.leader_id,
                                my_id: req.params.my_id,
                                limit: req.params.limit,
                                offset: req.params.offset
                            },
                        type: db.sequelize.QueryTypes.SELECT
                    })
                    .then(function(results){
                        user.is_followed = results[0].is_following;
                        return user
                    })
            })
            Promise.all(promises).then(function(results) {
                return res.status(200).send({
                    error: false,
                    message: users
                });
            })
                .catch(function (err) {
                    return res.status(400).send({
                        error: true,
                        message: errorHandler.getErrorMessage(err)
                    });
                });
        })
    .catch(function (err) {
        return res.status(400).send({
            error: true,
            message: errorHandler.getErrorMessage(err)
        });
    });
};

sequential queries from database using sequalize in node+express

3 Answers3