如何从DB中创建平面数组的嵌套注释数组

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:

在查询数据库以查找嵌套在闭包表中的注释之后,比如Bill Karwin在这里建议什么是将平面表解析为树的最有效/优雅的方法?现在我从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:

使用PHP我想重构这个数据集,所以注释嵌套如下:

"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?

我已经破解了一个解决方案,但它似乎过于复杂,我觉得有一个聪明的解决方案,以优雅和有效的方式做到这一点,但我不知道如何?

3 个解决方案

#1

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

假设您将所有数据提取到由“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:

我测试了以下内容,它可以生成您想要的JSON输出:

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

#2

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

我建议采用两阶段方法。第1阶段:构建嵌套数组第2阶段:将数组转换为JSON

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

只需通过基于面包屑创建元素即可处理第1阶段。例如,对于“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.

我不确定获得上述代码的最优雅方式是,通过将面包屑解析为其元素并在其中使用if-else语句。它可能是功能性的,但它可能不是最优雅的代码。

$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.

第2阶段可以使用PHP提供的json_encode()完成。

#3

$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.

这个解决方案需要一点点抛光。希望能帮助到你。

#1