recursive nodejs mysql query

Question

I want to execute a recursive function that retrieve data from DB. In php the code below run like a charm with 15ms to execute

function GetSubCategories($catno,&$subcats, $useactive=true){
   global $dbconn;

   $qid = new SSQL($dbconn, "SELECT categoryno FROM article_category WHERE parent = '$catno'".($useactive?" AND active = 'Y'":"")." ORDER BY sortorder");
   if ($qid->query()){
      while($catrow=$qid->fetch_array()){
        $subcats[]=$catrow["categoryno"];
        GetSubCategories($catrow["categoryno"],$subcats, $useactive);
      }
    }

}

I'm a newbie in nodejs environment and Async cause trouble in this case. If i write the same coe in js the program exit after first iteration. I can sync the process with await but execution time explode...

I try many thing with promise like

var getSubcategoriestest = function(categoryno,subcats, useactive=true){
return new Promise(async function (resolve) {
    const query = `SELECT categoryno FROM article_category WHERE ?? = ? ${useactive?" AND active = 'Y'":""} ORDER BY sortorder`
    let rows = await mysqlConn.query(query,['parent',categoryno])
    resolve(rows)
}).then((rows)=>{
    for (row of rows){
        console.log(row.categoryno)
        return new Promise(async function (resolve) {
            await getSubcategoriestest(row.categoryno,subcats, useactive)
            resolve()
        }).then(()=>{console.log('end')})
    } 
})

}

but nothing work fine

Any guru can help me ?

Thanks

Jeremy

I test this code

var getSubcategoriestest = async function(categoryno,subcats, useactive=true,arrPromise=[]){

let promise = new Promise(function (resolve,reject) {
    const query = `SELECT categoryno FROM article_category WHERE ?? = ? ${useactive?" AND active = 'Y'":""} ORDER BY sortorder`
    mysqlConn.query(query,['parent',categoryno]).then((rows)=>resolve(rows)).catch(err=>console.log(err))
}).then((rows)=>{
        for (row of rows){
            getSubcategoriestest(row.categoryno,subcats, useactive,arrPromise).then((rows)=>{subcats.push(row.categoryno)})
        }
        return row.categoryno

}) 

arrPromise.push(promise)

Promise.all(arrPromise).then(function() {
    console.log("promise all,") 
    return 
}).catch(err=>console.log(err))

}

but function end always after first iteration. Promise.all it's call many times (cause bind at each iteration i suppose)... headache,headache,headache

Try Promise.all in your loop. For every element add promise in array and run code .developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/… . Should be faster. await keyword stops execution and wait for result, while promise all run everything in parallel — Mykola Borysyuk
– Mykola Borysyuk, Commented Oct 4, 2018 at 14:02
thanks for reply, yeah i read about that, but to do this i need to keep an array for each promise. There no anything else more clean ? BTW i will try promise.all — bug00
– bug00, Commented Oct 4, 2018 at 14:07
No you need one array. And just push every promise in it. And you done. Then just Promise.all(yourArray).then((res) => you good run what you want).catch(if there is error) — Mykola Borysyuk
– Mykola Borysyuk, Commented Oct 4, 2018 at 14:32
I had edit my question, i had try this way but without success. Thanks for your time — bug00
– bug00, Commented Oct 4, 2018 at 21:39

bug00 · Accepted Answer · 2018-10-08 10:42:13Z

1

Here we go

var getSubcategoriestest = function (categoryno,subcats) {

   const query = `SELECT c FROM ac WHERE ?? = ? ORDER BY sortorder`

   return mysqlConn.query(query,['parent',categoryno]).then(rows => {   
        return Promise.all(rows.map(row => {
            subcats.push(row.categoryno);
            return getSubcategoriestest(row.categoryno, subcats,useactive);
        }));
   })}

rows.map make an array of promise cause getSubcategoriestest return a promise. You can add a then after promise.all.

answered Oct 8, 2018 at 10:42

bug00

112 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

recursive nodejs mysql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related