PHP中的MPTT（修改预订树遍历）问题 [英] MPTT ( Modified Preorder Tree Traversal) issue in PHP

问题描述

我的第一篇文章在这里！似乎是这样的明智的地方;）

My first post here! Seems like this is the place to get wise ;)

我目前正在尝试MPTT（修改预订树遍历）的第一次尝试中，在PHP的帮助下将数据存储在我的Mysql数据库中的方法。

I am currently in the middle of some testing with my first ever attempt to try the MPTT (Modified Preorder Tree Traversal) approach to storing data in my Mysql database with the help of PHP.

但是，我试图找出最具性能的方式来获取所有列表元素具体的级别，与特定的父母。

However, I am trying to find out the most performance-oriented way to get all list elements on a specific level, with a specific parent.

如果所输入的父项将被命名为Bilar，那么这可以转换为从下面的图像中获取类别Saab和Chrysler。 （这意味着汽车在瑞典语，如果这不是你最强的一面;））

That could translate into getting the categories Saab and Chrysler from the image below, if the parent entered would be named "Bilar". (Which means Cars in Swedish, if that isnt your strongest side ;) )

由于我不能发布图像，这里是一个链接到流程图： http://www.phpsidan.nu/files/mptt/mptt1.png

As i cant post images, here's a link to the flowchart: http://www.phpsidan.nu/files/mptt/mptt1.png

目前，我一直在做两个查询来做我想要的，并且有相当数量的代码来计算级别，并吐出所有其他元素级别。

Currently i have been doing two querys to do what i want, and quite some amount of code to calculate the level and spit out all the other elements on the same level.

有没有更好的方法来做到这一点，希望只能使用一个查询？

Is there a better way to do this and by hopefully only using one query?

谢谢很多！

推荐答案

我相信这可以进行优化，但是假设你有name，lft和 rgt，以下将给你Bilar的2级兄弟姐妹。

I am sure this could be optimized, however assuming you have the columns "name", "lft" and "rgt", the following will give you the level 2 siblings of "Bilar".

SELECT node.name,                                                                                                                                     
       node.lft AS sort,                                                                                                                                                                                                                                                               
       (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth                                                                        

FROM car AS node,                                                                                                                                  
     car AS parent,                                                                                                                                
             car AS sub_parent,                                                                                                                            
             ( SELECT node.name, (COUNT(parent.name) - 1) AS depth                                                                                          
                 FROM car AS node,                                                                                                                         
                      car AS parent                                                                                                                        
                WHERE node.lft BETWEEN parent.lft AND parent.rgt                                                                                            
                  AND node.name = "Bilar"                                                                                                                       
             GROUP BY node.name                                                                                                                             
             ORDER BY node.lft) AS sub_tree

WHERE node.lft BETWEEN parent.lft AND parent.rgt                                                                                                     
  AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt                                                                                             
  AND sub_parent.name = sub_tree.name

GROUP BY node.name HAVING depth <= 2                                                                                                                 
ORDER BY node.lft

这篇关于PHP中的MPTT（修改预订树遍历）问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！