搜索热词
我刚刚为这个幻灯片共享中的第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)
);
);
更新:
找到此链接,但我仍然很难将其转换为数组:
@L_301_1@
Update2:
我现在可以为每个类别添加深度,如果这可以有任何帮助.
@H_403_27@
好的,我编写了扩展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 |
| @R_301_126@ 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;