Query is not executed inside of a loop function nodejs MySQL

Question

I am a beginner in NodeJs and I'm trying to run a SQL query inside of a while, but it is not executing the query. I have a function in which I have an array of data from the database, if I got results, I'll save them in 2 arrays, then I declared an auxiliar variable for the while loop. Inside of the while I have to select all the members that have the reference_id in one of the arrays. If I get data, I have to save the data in the 2 arrays again and repeat the operation until there is no data from the database. The problem is that the query and all the operations that are inside of the query are not working and I don't know why.

everything inside of the loop is being executed just the query is the one that is not being executed.

   const membersPiramid2 = (request, response) => {
 let member_id = request.params.member_id;
 let members_ids = [];
 let members = [];
 let aux = true;

 db.query(`SELECT member_id FROM structures WHERE reference_id = '${member_id}'`, (error, results) => {
 if (error) throw error;

 if (results.length > 0) {
   members_ids = results.map(r => r.member_id);
   members.push(results);
  
   while (aux) {
    db.query(`SELECT member_id FROM structures WHERE reference_id IN ('${members_ids}')`, (err, newMembers) => {
      if (err) throw err;

      if (newMembers.length > 0) {
        members.push(newMembers);
        members_ids.length = 0
        members_ids = newMembers.map(m => m.member_id)

      } else {
        aux = false
      }
    })
  }
}
response.send(members);

I tried using promises and async functions but if I use any loop it is still not working

function submembersPiramid(members_ids) {
  return new Promise((resolve, reject) => {
    db.query(`SELECT member_id FROM structures WHERE reference_id IN ('${members_ids}')`, (err, newMembers) => {
    console.log('hola2')
    if (newMembers.length > 0) {
     resolve(newMembers)
    } else {
     reject('No data found')
    }
   })
})
}




 const membersPiramid = (request, response) => {
  let member_id = request.params.member_id;
  let members_ids = [];
  let members = [];
  let aux = true;

  db.query(`SELECT member_id FROM structures WHERE reference_id = '${member_id}'`, (error, results) => {
  if (error) throw error;

  if (results.length > 0) {
    members_ids = results.map(r => r.member_id);
    members.push(results);
  
    async function doFunction() {
      await submembersPiramid(members_ids).then(response => {
        if (response != 'No data found') {
          members.push(response);
          members_ids.length = 0;
          members_ids = response.map(r => r.member_id)
        } else {
          aux = false;
        }
      }).then()
    }
    while(aux){

      doFunction()
    }


  }
  response.send(members);
})
}

Any idea why the query does not work in any loop?

Here is the query result enter image description here

it didn't work, the error I guess is not in the query, because the query works properly, let me edit the post in order that you see the query result. — , Jul 15 '22 at 18:26
This would be better solved in a **single query** by joining the table to itself. — Joel Coehoorn, Jul 15 '22 at 18:57

nbk · Answer 1 · 2022-07-19T15:33:40.350

instead of two queries , you can do it all in one alone

db.query(`SELECT  member_id FROM structures WHERE reference_id IN (SELECT member_id FROM structures WHERE reference_id = '${member_id}') UNION SELECT member_id FROM structures WHERE reference_id = '${member_id}'`
 , (err, newMembers) => {
  if (err) throw err;

  if (newMembers.length > 0) {
    members.push(newMembers);
    members_ids.length = 0
    members_ids = newMembers.map(m => m.member_id)

  } else {
    aux = false
  }
})

in case you really want to use nested queries, which in rare occasions it is necessary, you should see the solutions here where the asyncron communicaion is mandatory Nested query in node js using mysql

and when we are at it, you colud always look also this about sql injection up Preventing SQL injection in Node.js

the query to get all member_ids must be

SELECT 
    member_id
FROM
    structures
WHERE
    reference_id IN (SELECT 
            member_id
        FROM
            structures
        WHERE
            reference_id = '${member_id}') 
UNION SELECT 
    member_id
FROM
    structures
WHERE
    reference_id = '${member_id}'

I cannot do it like that because the result of the second query (the one inside the while) will bring me new ids with which I have to repeat the query until the result of it is empty. Basically, I have to get the linked ids of the members_ids array which contains the ids that are linked to the results of the first query, so with the result of the second query, I clear the members_ids array to add the new ones and repeat this process until the result of the second query is empty. — , Jul 19 '22 at 15:15
that is no problem, i changed the mysql query so that you get both id — nbk, Jul 19 '22 at 15:34

score 0 · Answer 2 · answered Jul 15 '22 at 19:02

0

Something like this is likely to be simpler and many times more efficient:

SELECT p.member_id as parent_member_id, c.member_id as child_member_id
FROM structures p
INNER JOIN structures c on c.reference_id = p.member_id
WHERE p.reference_id = '${member_id}'

While we're here, this looks like it would be crazy-vulnerable to sql injection. Sql injection is big deal, so take a few moments to make sure you understand what sql injection is and your platform's mechanism to use prepared statements/parameterized queries.

answered Jul 15 '22 at 19:02

Joel Coehoorn

399,467
113
570
794

I cannot use a JOIN because the result of the second query (the one inside the while) will bring me new ids with which I have to repeat the query until the result of it is empty. I have to get the linked ids of the members_ids array which contains the ids that are linked to the results of the first query, so with the result of the second query, I clear the members_ids array to add the new ones and repeat this process until the result of the second query is empty. – Jul 19 '22 at 15:15
Nothing in there means you can't use a JOIN, but it does mean you may need another layer via nested query and/or GROUP BY. Also maybe a windowing function. – Joel Coehoorn Jul 19 '22 at 15:22

Query is not executed inside of a loop function nodejs MySQL

2 Answers2