Wsrtjtyk

I have a database where parent records can have unlimited levels of children. The following simplified records illustrate:

id: 0, parent: null

id: 1, parent: 0

id: 2, parent: 0

id: 3, parent: 2

I have been trying for weeks now to write a recursive function in node/express that generates a javascript object representing this tree of records and NOT returning until it is complete. I can do this no problem as long as I include a timeout, but clearly that is sub-optimal.

This is my most recent attempt:

var getChildren = function (parent, callback) {

    const query = "SELECT ID, ParentID, NULL AS Children FROM myTable WHERE "ParentID = '" + parent.ID + "'";

    db.doQuery (query, function (err, results) {

        if(err) {

            callback (true);

        } else {

            if (results.length == 0) callback (false, parent);  // We didn't find any children so we end the recursion

            parent.Children = results;  // Each time we get a result set we need to add that set to our Children property

            // Iterate through our result set to find the next layer of children

            var i = 0;

            async.whilst (

                function () { return i < results.length },

                function (callback) {

                    i++;

                    getChildren(parent.Children[i - 1], function (err, results) {

                        if (err) callback (true);

                    })

                },

                callback (false, parent)

            );

        }

    })

}

In short this is a recursive routine that reads all children as records from a database. It adds this result set to the object property 'Children' and then it then iterates through the result set and for each one recursively calls itself to find more children of that record. The JSON that is ultimately returned looks like this:

{

      "ID": "000000000",

      "ParentID": null,

      "Children": [

        {

          "ID": "000000001",

          "ParentID": "000000000",

          "Children": null

        },

        {

          "ID": "000000002",

          "ParentID": "000000000",

          "Children": [

            {

              "ID": "000000003",

              "ParentID": "000000002",

              "Children": null

            }

          ]

        }

      ]

}

As you can probably already tell, what actually happens is the code above returns only the first level of children until I put a timeout in the calling function. I have tried several async functions, I have tried counting timeouts, I've tried re-writing for loops with their own recursive functions with callback, and I cannot for the life of me get this to work. I know that the problem is that I just can't wrap my head around some async magic that will help me identify when the actual recursion is completely finished and callback to the calling function.

Is this a case where I have to keep track of the levels of recursion I have called and then count them down as they callback before I use a final callback?

Any insight would be greatly appreciated.