How to create array of nested comments out of flat array from DB

Question

After querying the DB for comments that are nested in a closure table, like Bill Karwin suggests here What is the most efficient/elegant way to parse a flat table into a tree?, I now get the following datastructure from SQL:

"comments": [
            {
                "id": "1",
                "breadcrumbs": "1",
                "body": "Bell pepper melon mung."
            },
            {
                "id": "2",
                "breadcrumbs": "1,2",
                "body": "Pea sprouts green bean."
            },
            {
                "id": "3",
                "breadcrumbs": "1,3",
                "body": "Komatsuna plantain spinach sorrel."
            },
            {
                "id": "4",
                "breadcrumbs": "1,2,4",
                "body": "Rock melon grape parsnip."
            },
            {
                "id": "5",
                "breadcrumbs": "5",
                "body": "Ricebean spring onion grape."
            },
            {
                "id": "6",
                "breadcrumbs": "5,6",
                "body": "Chestnut kohlrabi parsnip daikon."
            }
        ]

Using PHP I would like to restructure this dataset, so the comments are nested like this:

"comments": [
            {
                "id": "1",
                "breadcrumbs": "1",
                "body": "Bell pepper melon mung."
                "comments": [
                    {
                        "id": "2",
                        "breadcrumbs": "1,2",
                        "body": "Pea sprouts green bean."
                        "comments": [
                            {
                                "id": "4",
                                "breadcrumbs": "1,2,4",
                                "body": "Rock melon grape parsnip."
                            }
                        ]
                    },
                    {
                        "id": "3",
                        "breadcrumbs": "1,3",
                        "body": "Komatsuna plantain spinach sorrel."
                    }
                ]
            },
            {
                "id": "5",
                "breadcrumbs": "5",
                "body": "Ricebean spring onion grape."
                "comments": [
                    {
                        "id": "6",
                        "breadcrumbs": "5,6",
                        "body": "Chestnut kohlrabi parsnip daikon."
                    }
                ]
            }
        ]

I have hacked together a solution, but it seems over complex, and I have a feeling that there is some clever solution out there to do this in an elegant and efficient way, but I dont know how?

Answer 1

Assuming you fetch all your data into an array indexed by the "id":

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $nodes[$row["id"]] = $row;
}

I tested the following and it works to produce the JSON output you want:

foreach ($nodes as &$node) {
        $parent = array_shift(array_slice(explode(",",$node["breadcrumbs"]), -2, 1));
        if ($parent == $node["id"]) {
                $forest["comments"][] = &$node;
        } else {
                $nodes[$parent]["comments"][] = &$node;
        }
}

print json_encode($forest, JSON_PRETTY_PRINT);