我刚刚为这个幻灯片共享中的第70页所示的组织查询分层数据的“Closure表”方式进行了更新/添加/删除部分：
http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back
我的数据库看起来像这样：
表分类：
ID Name 1 Top value 2 Sub value1
表类别树：
child parent level 1 1 0 2 2 0 2 1 1
但是,从单个查询中将完整的树作为多维数组返回时,我遇到了一些问题.
这是我想要回来的：
array (
'topvalue' = array (
'Subvalue','Subvalue2','Subvalue3)
);
);
更新：
找到此链接,但我仍然很难将其转换为数组：
http://karwin.blogspot.com/2010/03/rendering-trees-with-closure-tables.html
Update2：
我现在可以为每个类别添加深度,如果这可以有任何帮助.
好的,我编写了扩展Zend Framework DB表,行和行集类的PHP类.无论如何,我一直在开发这个,因为我在几个星期后在
PHP Tek-X讲关于分层数据模型.
我不想将我的所有代码发布到Stack Overflow,因为如果我这样做,他们会隐式获得Creative Commons的许可.更新：我将我的代码提交到Zend Framework extras incubator,我的演示文稿是Models for Hierarchical Data with SQL and PHP在slideshare.
我将用伪代码描述解决方案.我使用动物分类学作为测试数据,从ITIS.gov下载.该表是长名称：
CREATE TABLE `longnames` ( `tsn` int(11) NOT NULL,`completename` varchar(164) NOT NULL,PRIMARY KEY (`tsn`),KEY `tsn` (`tsn`,`completename`) )
我已经为分类法层次结构中的路径创建了一个闭包表：
CREATE TABLE `closure` ( `a` int(11) NOT NULL DEFAULT '0',-- ancestor `d` int(11) NOT NULL DEFAULT '0',-- descendant `l` tinyint(3) unsigned NOT NULL,-- levels between a and d PRIMARY KEY (`a`,`d`),CONSTRAINT `closure_ibfk_1` FOREIGN KEY (`a`) REFERENCES `longnames` (`tsn`),CONSTRAINT `closure_ibfk_2` FOREIGN KEY (`d`) REFERENCES `longnames` (`tsn`) )
给定一个节点的主键,您可以通过这种方式获取其所有后代：
SELECT d.*,p.a AS `_parent` FROM longnames AS a JOIN closure AS c ON (c.a = a.tsn) JOIN longnames AS d ON (c.d = d.tsn) LEFT OUTER JOIN closure AS p ON (p.d = d.tsn AND p.l = 1) WHERE a.tsn = ? AND c.l <= ? ORDER BY c.l;
连接到闭包AS p是包括每个节点的父ID.
该查询很好地利用了索引：
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+ | 1 | SIMPLE | a | const | PRIMARY,tsn | PRIMARY | 4 | const | 1 | Using index; Using filesort | | 1 | SIMPLE | c | ref | PRIMARY,d | PRIMARY | 4 | const | 5346 | Using where | | 1 | SIMPLE | d | eq_ref | PRIMARY,tsn | PRIMARY | 4 | itis.c.d | 1 | | | 1 | SIMPLE | p | ref | d | d | 4 | itis.c.d | 3 | | +----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+
鉴于我在长名中有490,032行,在封闭中有4,299,883行,它运行得非常好：
+--------------------+----------+ | Status | Duration | +--------------------+----------+ | starting | 0.000257 | | opening tables | 0.000028 | | System lock | 0.000009 | | Table lock | 0.000013 | | init | 0.000048 | | optimizing | 0.000032 | | statistics | 0.000142 | | preparing | 0.000048 | | executing | 0.000008 | | Sorting result | 0.034102 | | Sending data | 0.001300 | | end | 0.000018 | | query end | 0.000005 | | freeing items | 0.012191 | | logging slow query | 0.000008 | | cleaning up | 0.000007 | +--------------------+----------+
现在我对上面的SQL查询的结果进行后处理,根据层次结构(伪代码)将行排序为子集：
while ($rowData = fetch()) {
$row = new RowObject($rowData);
$nodes[$row["tsn"]] = $row;
if (array_key_exists($row["_parent"],$nodes)) {
$nodes[$row["_parent"]]->addChildRow($row);
} else {
$top = $row;
}
}
return $top;
我还为行和行集定义了类. Rowset基本上是一个行数组. A Row包含行数据的关联数组,并且还包含其子项的Rowset.叶节点的子行Rowset为空.
行和行集还定义了名为toArrayDeep()的方法,这些方法以递归方式将其数据内容转储为普通数组.
然后我可以像这样一起使用整个系统：
// Get an instance of the taxonomy table data gateway $tax = new Taxonomy(); // query tree starting at Rodentia (id 180130),to a depth of 2 $tree = $tax->fetchTree(180130,2); // dump out the array var_export($tree->toArrayDeep());
输出如下：
array (
'tsn' => '180130','completename' => 'Rodentia','_parent' => '179925','_children' =>
array (
0 =>
array (
'tsn' => '584569','completename' => 'Hystricognatha','_parent' => '180130','_children' =>
array (
0 =>
array (
'tsn' => '552299','completename' => 'Hystricognathi','_parent' => '584569',),1 =>
array (
'tsn' => '180134','completename' => 'Sciuromorpha','_children' =>
array (
0 =>
array (
'tsn' => '180210','completename' => 'Castoridae','_parent' => '180134',1 =>
array (
'tsn' => '180135','completename' => 'Sciuridae',2 =>
array (
'tsn' => '180131','completename' => 'Aplodontiidae',2 =>
array (
'tsn' => '573166','completename' => 'Anomaluromorpha','_children' =>
array (
0 =>
array (
'tsn' => '573168','completename' => 'Anomaluridae','_parent' => '573166',1 =>
array (
'tsn' => '573169','completename' => 'Pedetidae',3 =>
array (
'tsn' => '180273','completename' => 'Myomorpha','_children' =>
array (
0 =>
array (
'tsn' => '180399','completename' => 'Dipodidae','_parent' => '180273',1 =>
array (
'tsn' => '180360','completename' => 'Muridae',2 =>
array (
'tsn' => '180231','completename' => 'Heteromyidae',3 =>
array (
'tsn' => '180213','completename' => 'Geomyidae',4 =>
array (
'tsn' => '584940','completename' => 'Myoxidae',4 =>
array (
'tsn' => '573167','completename' => 'Sciuravida','_children' =>
array (
0 =>
array (
'tsn' => '573170','completename' => 'Ctenodactylidae','_parent' => '573167',)
重新评论有关计算深度 – 或每条路径的实际长度.
假设您刚刚在表中插入了一个包含实际节点的新节点(上例中的longname),新节点的id由MysqL中的LAST_INSERT_ID()返回,否则您可以以某种方式获取它.
INSERT INTO Closure (a,d,l) SELECT a,LAST_INSERT_ID(),l+1 FROM Closure WHERE d = 5 -- the intended parent of your new node UNION ALL SELECT LAST_INSERT_ID(),0;
