PHP：从嵌套集中排序数据

We're currently building a website with a categorized MySQL table containing various competences, and we noticed that the nested set model would be optimized for this. Although, we've got a pretty serious problem - the nested set model doesn't allow any sorting, and we really need that possibility. I'd like the output data to be array(id, name, depth), as this function supports (though without any kind of sorting):

我们目前正在构建一个包含各种权限的分类MySQL表的网站,我们注意到嵌套集模型将针对此进行优化。虽然,我们有一个非常严重的问题 - 嵌套集模型不允许任何排序,我们真的需要这种可能性。我希望输出数据是数组(id,name,depth),因为这个函数支持(虽然没有任何排序):

function tree()
{
    $query = 'SELECT node.id, node.name, (COUNT(parent.name) - 1) AS depth FROM test_competence AS node, test_competence AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt GROUP BY node.name ORDER BY node.lft';
    $result = mysql_query($query) or die(mysql_error());

    while($data = mysql_fetch_assoc($result))
    {
        $returnarray[] = $data;
    }

    return $returnarray;
}

I've started with a function but have no idea how to continue:

我已经开始使用函数但不知道如何继续:

function tree_sorted()
{
    //Get data
    $query = 'SELECT node.id, node.name, node.parent, (COUNT(parent.name) - 1) AS depth FROM test_competence AS node, test_competence AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt GROUP BY node.name ORDER BY node.lft';
    $result = mysql_query($query) or die(mysql_error());

    //Fetch gotten data
    while($data = mysql_fetch_assoc($result))
    {
        $fetched[$data['depth']][$data['id']] = array($data['name'], $data['parent']);
    }

    //Sort fetched data
    foreach($fetched as $i => $row)
    {
        asort($row);
        $sorted[$i] = $row;
    }

    //Merge sorted data (???)
    foreach($sorted as $i => $arr)
    {
        foreach($arr as $x => $row)
        {
            $returnarray[] = array('id' => key($row), 'name' => $row[0], 'depth' => $x);
        }
    }

Any help would be greatly appreciated. I've googled for different ways to sort data from nested sets, but without any good result.

任何帮助将不胜感激。我用谷歌搜索了不同的方法来从嵌套集中排序数据,但没有任何好结果。

Thank you in advance.

先感谢您。

EDIT: Now I've tried some with the uasort() function which feels to be the right way, but the problem still remain.

编辑:现在我尝试了一些uasort()函数,感觉是正确的方法,但问题仍然存在。

3 个解决方案

#1

If you need sort a set of nodes in a tree, and maintain unlimited numbers of levels in the tree, might I recommend using pre-ordered tree traversal?

如果您需要在树中对一组节点进行排序,并在树中维护无限数量的级别,我是否可以建议使用预先排序的树遍历?

See http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ for an example implementation.

有关示例实现,请参见http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/。

The point is that you maintain a left and right value for each node. You could also maintain a depth column for each node, which tells which level of the tree it is in. You can use these left and right values to sort nodes by their order in the tree, and use the depth value to only select a given number of levels of the tree.

关键是您为每个节点维护左右值。您还可以为每个节点维护一个深度列,告诉它所在树的哪个级别。您可以使用这些左右值按树中的顺序对节点进行排序,并使用深度值仅选择给定的树的层数。

The only notable downside of this approach is that you have to actively maintain those left and right values when altering the structure of the nodes.

这种方法唯一值得注意的缺点是,在更改节点结构时,必须主动维护这些左右值。

#2

In my experience using the nested set model is not really necessary unless you expect some really heavy traffic. I'm not sure what exactly you need the hierarchy for, but I would recommend checking whether a simple parent-son-table with a cache in front of it wouldn't suffice, it's much easier to maintain and work with

根据我的经验,使用嵌套集模型并不是必需的,除非你期望一些非常繁忙的流量。我不确定你究竟需要什么样的层次结构,但我建议检查一个简单的父子表是否在其前面有一个缓存是不够的,它更容易维护和使用

Again, this of course depends on your application and how worried you are about performance issues.

同样,这当然取决于您的应用程序以及您对性能问题的担忧程度。

#3

Taking a stab in the dark, since nested set-tree data is, by definition, already sorted, it sounds like you need to convert the data into another format (usually a flat one) in order to sort it. The easiest way to achieve this is to simply work through the data, creating a flat dataset as you go.

在黑暗中进行刺穿,因为根据定义,嵌套的树集数据已经被排序,听起来你需要将数据转换成另一种格式(通常是平面格式)以便对其进行排序。实现这一目标的最简单方法是简单地处理数据,随时创建平面数据集。

You do already have a few options in the SQL. Ordering by the Left ID gets you in-order traversal, if I have the terminology correct. This is usually what people want when they list a set-tree as it makes sense when flattened into a list. I'd be experimenting with the ORDER BY clause in the SQL; for example, ordering by the depth parameter would give you a level-order traversal. Try combining that with node.name.

您在SQL中已经有了一些选项。如果我的术语正确,按左侧ID排序可以按顺序遍历。这通常是人们在列出集合树时所需要的,因为它在扁平化为列表时是有意义的。我将在SQL中尝试ORDER BY子句;例如,按深度参数排序将为您提供水平顺序遍历。尝试将其与node.name结合使用。

#1