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!

score 0 · Answer 1 · answered Dec 04 '20 at 04:20

0

It will be much more easy if you use asyc function

not sure if it work but i will try this:

async function GetReferrals(referrals) {
    if(!referrals) return [] // you need a condition to stop recursion

    let promises = referrals.map(amb =>knex.select('member_id').from('users').where({referred_by: 
        amb.member_id}))

    let res = await Promise.all(promises)

    return [...res, ...res.map(r => await GetReferrals(r)) ]
});

and on the main call:

GetReferrals(referrals).then( result =>
        // do something with the global_referral_array but
        // have to deal with the promises first
        res.status(200).send({final_referral_list});
)

answered Dec 04 '20 at 04:20

Sarabadu

587
2
18

Thank you for this! I did forget to put the conditional in the recursive function - I updated it...in any case - I will try your approach...one question: what will "result" actually have? The comment I put in is an actual point of confusion - how do I process the returned result? Thanks again for this! – Elliot Barnett Dec 04 '20 at 04:47
`result` is an array whit all the `referrals.map(amb =>knex.select('member_id').from('users').where({referred_by: amb.member_id}))` resultsif you try it, let me know if it works, thanks!! – Sarabadu Dec 04 '20 at 04:50

richytong · Accepted Answer · 2020-12-04T05:24:26.607

0

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);
    })
});

edited Dec 04 '20 at 05:24

answered Dec 04 '20 at 04:49

richytong

2,387
1
10
21

Thank you for taking the time...I actually get Error in select TypeError: referralsReferrals.flat is not a function - I'm using node v10.15.0...I'd think flat was supported...no? I was wondering if there was a way to actually have a select call once...thanks for providing a possibility that covers that case! – Elliot Barnett Dec 04 '20 at 05:09
ah yes, seems flat is actually supported in nodejs v11 and beyond. I'll amend that. Regarding a single select call.. that might be possible if you use raw SQL. What's the expected traffic/load for this particular endpoint? – richytong Dec 04 '20 at 05:22
also make sure you have an index on `referred_by` for your users table – richytong Dec 04 '20 at 05:28
[this question](https://stackoverflow.com/questions/18463634/how-to-flatten-a-postgresql-result) may give you some ideas about how to do it in SQL. – richytong Dec 04 '20 at 06:10
1

Wow! That worked! I really appreciate your help here...I've been stuck on this big time - I need to study this a bit more...I'm still more of a hack than I should be at this point - again...thanks so much! – Elliot Barnett Dec 04 '20 at 06:40

javascript recursive function with database promises

2 Answers2