Recursive query issue with MySQL

Question

I'm building a recursive query:

app.get('/showposts', function(res, req) {

  getPosts(id){

    connection.query("SELECT * FROM posts WHERE someId = 2", (err, rows) => {

      allIds = [];

        for(var i = 0; i < rows.length; i++){
          allIds.push(rows[i].id_post);
          getPosts(rows[i].id_post)     // <= calling the function again.
        }

        console.log("The IDs : " + allIds )  
        // res.send fails stating response was already sent

        });
    };

    getPosts(1)  // <= initial call to the function.
});

This works well except that the response is sent for each and every time. So if there are 5 results (3, 7, 8, 11, 12) when calling the function first time and calling it again from the for loop there is 1 result (13, cause for this id_post, someId=3) I get :

I want to send only 1 response with the entire array. But if I do res.send, I get error:

Error: Can't set headers after they are sent.
    at ServerResponse.OutgoingMessage.setHeader (_http_outgoing.js:356:11)

What am I doing wrong? Please help.

Answer 1

You are using an asynchronous function as it was synchronous.

Take a look at Promise, async/await pattern.

Look here

---

EDIT : as you asked - didn't tried it, this is a start

  getPosts(id) {
    return new Promise((resolve, reject) => {
      connection.query(
        `SELECT * FROM posts WHERE someId = ${id}`,
        (err, rows) => {
          // Nothing more to do, we can leave
          if (!rows.length) return resolve([]);

          // Call the getPosts on the new ids we just read
          return Promise.all(rows.map(x => getPosts(x.id_post)))
            .then((rets) => {
              // We return the ids returned from the recursive 
              // getPosts and from the actual getPosts

              // We want resolve to return something like
              // [ id1, id2, id3, ... ]
              resolve([
                // rets here worth something like :
                //  
                //  [  [ id1, id2, id3 ...], [ id4, id5, id6 ...], ... ]
                //  
                // we gonna turn it into [ id1, id2, id3, id4, id5, id6, ... ]
                // 
                ...rets.reduce((tmp, x) => [...x, ...tmp], []),
                ...rows.map(x => x.id_post),
              ]);
            })
            .catch(reject);
      });
    });
  };

    // call the func
    getPosts(1)
     .then(ids => console.log(ids))
     .catch(err => console.log(String(err)));