javascript recursive function with database promises

Question

I have spent an embarrassing amount of time on this. I can find several possible solutions but I can't seem to map them to my specific situation. The basic problem is my lack of understanding of how to manage the promises. this is very close and likely a solution I could use but I have a variation that makes it not work directly as needed.

I have a user table in my database. Any user can be an affiliate and any affiliate can have 0 to n number of referrals. Any of those referrals can also refer members. Given an affiliate's member_id, I need to get a list of all their referrals and all their referrals, referrals...and etc so it is a basic recursion problem. The challenge is...I can't seem to get the promise deal correct.

Things kick of with an api call:

app.get('/api/calculateReferralTotals', (req, res) => {
    const member_id = req.body.member_id;
    knex.select('member_id').from('users').where({referred_by: 
    member_id}).then((referrals) => {
        GetReferrals(referrals);
        // do something with the global_referral_array but
        // have to deal with the promises first
        res.status(200).send({final_referral_list});
    }).catch((error) => {
        console.log("Error in select " + error);
    });
});

var global_referral_array = [];
function GetReferrals(referrals) {
    referrals.forEach((amb) => {
        knex.select('member_id').from('users').where({referred_by: 
        amb.member_id}).then((ref) => {
            if(ref.length>0) {
            ref.forEach((element) => {
                global_referral_array.push(element.member_id);
            });
                GetReferrals(ref);
            }
        }).catch((error) => {
    });
});

The recursive function does what I expect - I get a list of the referrals member_ids, but obviously I have to deal with the async nature somehow and that's where I'm stuck. I thought I could build an array of promises through the recursion and then resolve those upon returning to the original call (maybe have a global promise array) but that didn't seem to work. I also tried just passing a single member_id to the recursive function and continue to loop after the DB call so I'm just dealing with a single occurrence but that didn't work either.

Any guidance would be much appreciated!

Answer 1

This should work.

function GetReferrals(member_id) {
  return knex.select('member_id').from('users').where({referred_by: 
  member_id}).then((referrals) => {
    const referralIds = referrals.map(element => element.member_id)
    return Promise.all(referralIds.map(GetReferrals)).then(referralsReferrals => {
      return referralsReferrals.reduce((final, arr) => final.concat(arr), referralIds)
    })
  })
}

app.get('/api/calculateReferralTotals', (req, res) => {
    const member_id = req.body.member_id;
    GetReferrals(member_id).then(final_referral_list => {
      // do something with the global_referral_array
      res.status(200).send({final_referral_list})
    }).catch((error) => {
      console.log("Error in select " + error);
    })
});