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?

One loop, add children by reference? This is an example I posted to SO earlier for an adjacency table, but it works equally well for this one as long as you get the direct parent from the breadcumbs (the item before last). — Wrikken
– Wrikken, Commented Oct 13, 2013 at 18:37
@Wrikken Thank you very much for answering, that looks like a nice solution. However, I need the output to match the exact datastructure posted above, as I am sending it as JSON to the front end, that needs array of objects to properly function, so I cant use the id's as array keys as you do -and that seems to complicate things a bit. — acrmuui
– acrmuui, Commented Oct 13, 2013 at 18:56
@acrmuul: yes, that is a solution that you need to tinker a bit with, but it basically comes down to renaming 'children' to 'comments' in that code. Are you having more problems with it then that? If so, which? — Wrikken
– Wrikken, Commented Oct 13, 2013 at 18:59
Also, that code only uses the indexes for the building fase, by the line $tree[$parentid]['children'][] = &$tree[$item['id']] you can clearly see we use no named keys for the child items themselves... — Wrikken
– Wrikken, Commented Oct 13, 2013 at 19:25
@Wrikken Ah now I got it to work and understand the = &$tree[$item['id']] part, very very nice ! — acrmuui
– acrmuui, Commented Oct 13, 2013 at 21:01

Bill Karwin · Accepted Answer · 2014-11-24 17:30:14Z

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);

answered Nov 24, 2014 at 17:30

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

Comments

BenMorel · Accepted Answer · 2014-01-31 14:01:36Z

I would suggest a 2 stage approach. Stage 1 : Build an nested array Stage 2 : Convert array to JSON

Stage 1 can be handled simply by creating your elements based on your breadcrumbs. For example, for "breadcrumbs": "1,2,4"

$comments_array[1][2][4] = $current_element_from_flat_array;

I'm not sure what the most elegant way to get to the above code is, perhaps by parsing the breadcrumbs into its element and having if-else statements based in this. It might be functional, but It's probably not the most elegant code.

$breadcrumbs_list = explode(",", $pizza);
if (count($breadcrumbs_list) == 2)
    $comments_array[$breadcrumbs_list[1]][$breadcrumbs_list[2]] = $current_element_from_flat_array;
else if (count($breadcrumbs_list) == 3)
    $comments_array[$breadcrumbs_list[1]][$breadcrumbs_list[2]][$breadcrumbs_list[1]] = $current_element_from_flat_array;

Stage 2 can be done using json_encode() provided by PHP.

Koofah · Accepted Answer · 2014-11-24 02:09:06Z

$tree = array('NULL' => array('children' => array()));
 foreach($array as $item){
    if(isset($tree[$item['id']])){
       $tree[$item['id']] = array_merge($tree[$item['id']],$item);
    } else {
       $tree[$item['id']] = $item;
    }

    $parentid = is_null($item['id_parent']) ? 'NULL' : $item['id_parent'];
    if(!isset($tree[$parentid])) $tree[$parentid] = array('children' => array());
    //this & is where the magic happens: any alteration to $tree[$item['id']
    //  will reflect in the item $tree[$parentid]['children'] as they are the same
    //  variable. For instance, adding a child to $tree[$item['id']]['children]
    //  will be seen in 
    //  $tree[$parentid]['children'][<whatever index $item['id'] has>]['children]
    $tree[$parentid]['children'][] = &$tree[$item['id']];
 }
 $result = $tree['NULL']['children'];
 //always unset references
 unset($tree);

This solution needs a little polishing. Hope it helps.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related