将数据库结果转化为数组 [英] Turn database result into array
问题描述
我刚刚为关闭表"组织查询分层数据的方式进行了更新/添加/删除部分，这些数据显示在此幻灯片共享中的第 70 页:http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back
I have just made the update/add/delete part for the "Closure table" way of organizing query hierarchical data that are shown on page 70 in this slideshare: 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
但是，我在通过单个查询将完整树作为多维数组返回时遇到了一些问题.
However, I have a bit of an issue getting the full tree back as an multidimensional array from a single query.
这是我想要的:
array (
'topvalue' = array (
'Subvalue',
'Subvalue2',
'Subvalue3)
);
);
更新:找到这个链接，但我仍然很难将其转换为数组:http://karwin.blogspot.com/2010/03/rendering-trees-with-closure-tables.html
Update: Found this link, but I still have a hard time to convert it into an array: http://karwin.blogspot.com/2010/03/rendering-trees-with-closure-tables.html
更新 2 :我现在可以为每个类别添加深度(如果有帮助的话).
Update2 : I was able to add depths to each of the categories now, if that can be of any help.
推荐答案
好的，我已经编写了扩展 Zend Framework DB 表、行和行集类的 PHP 类.我一直在开发这个，因为我在几周内在 PHP Tek-X 上谈论分层数据模型.
Okay, I've written PHP classes that extend the Zend Framework DB table, row, and rowset classes. I've been developing this anyway because I'm speaking at PHP Tek-X in a couple of weeks about hierarchical data models.
我不想将我所有的代码都发布到 Stack Overflow，因为如果我这样做，它们会隐式地获得知识共享许可.更新:我将我的代码提交给了Zend Framework extras incubator 而我的演示文稿是 使用 SQL 和 PHP 的分层数据模型在幻灯片共享中.
I don't want to post all my code to Stack Overflow because they implicitly get licensed under Creative Commons if I do that. update: I committed my code to the Zend Framework extras incubator and my presentation is Models for Hierarchical Data with SQL and PHP at slideshare.
我将用伪代码描述解决方案.我使用动物学分类作为测试数据，从 ITIS.gov 下载.该表是longnames:
I'll describe the solution in pseudocode. I'm using zoological taxonomy as test data, downloaded from ITIS.gov. The table is longnames:
CREATE TABLE `longnames` (
`tsn` int(11) NOT NULL,
`completename` varchar(164) NOT NULL,
PRIMARY KEY (`tsn`),
KEY `tsn` (`tsn`,`completename`)
)
我为分类层次结构中的路径创建了一个闭包表:
I've created a closure table for the paths in the hierarchy of taxonomy:
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`)
)
给定一个节点的主键，你可以这样得到它的所有后代:
Given the primary key of one node, you can get all its descendants this way:
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;
加入closure AS p 是包含每个节点的父id.
The join to closure AS p is to include each node's parent id.
查询很好地利用了索引:
The query makes pretty good use of indexes:
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+
| 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 | |
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+
考虑到我在 longnames 中有 490,032 行，在 closure 中有 4,299,883 行，它运行得非常好:
And given that I have 490,032 rows in longnames and 4,299,883 rows in closure, it runs in pretty good time:
+--------------------+----------+
| 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 查询的结果进行后处理，根据层次结构(伪代码)将行排序为子集:
Now I post-process the result of the SQL query above, sorting the rows into subsets according to the hierarchy (pseudocode):
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;
我还为行和行集定义了类.行集基本上是一个行数组.行包含行数据的关联数组，还包含其子项的行集.叶节点的子行集为空.
I also define classes for Rows and Rowsets. A Rowset is basically an array of rows. A Row contains an associative array of row data, and also contains a Rowset for its children. The children Rowset for a leaf node is empty.
Rows 和 Rowsets 还定义了称为 toArrayDeep() 的方法，这些方法将它们的数据内容递归地转储为一个普通数组.
Rows and Rowsets also define methods called toArrayDeep() which dump their data content recursively as a plain array.
然后我可以像这样一起使用整个系统:
Then I can use the whole system together like this:
// 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',
'_parent' => '180130',
'_children' =>
array (
0 =>
array (
'tsn' => '180210',
'completename' => 'Castoridae',
'_parent' => '180134',
),
1 =>
array (
'tsn' => '180135',
'completename' => 'Sciuridae',
'_parent' => '180134',
),
2 =>
array (
'tsn' => '180131',
'completename' => 'Aplodontiidae',
'_parent' => '180134',
),
),
),
2 =>
array (
'tsn' => '573166',
'completename' => 'Anomaluromorpha',
'_parent' => '180130',
'_children' =>
array (
0 =>
array (
'tsn' => '573168',
'completename' => 'Anomaluridae',
'_parent' => '573166',
),
1 =>
array (
'tsn' => '573169',
'completename' => 'Pedetidae',
'_parent' => '573166',
),
),
),
3 =>
array (
'tsn' => '180273',
'completename' => 'Myomorpha',
'_parent' => '180130',
'_children' =>
array (
0 =>
array (
'tsn' => '180399',
'completename' => 'Dipodidae',
'_parent' => '180273',
),
1 =>
array (
'tsn' => '180360',
'completename' => 'Muridae',
'_parent' => '180273',
),
2 =>
array (
'tsn' => '180231',
'completename' => 'Heteromyidae',
'_parent' => '180273',
),
3 =>
array (
'tsn' => '180213',
'completename' => 'Geomyidae',
'_parent' => '180273',
),
4 =>
array (
'tsn' => '584940',
'completename' => 'Myoxidae',
'_parent' => '180273',
),
),
),
4 =>
array (
'tsn' => '573167',
'completename' => 'Sciuravida',
'_parent' => '180130',
'_children' =>
array (
0 =>
array (
'tsn' => '573170',
'completename' => 'Ctenodactylidae',
'_parent' => '573167',
),
),
),
),
)
<小时>
重新考虑计算深度——或者每条路径的实际长度.
Re your comment about calculating depth -- or really length of each path.
假设您刚刚在包含实际节点的表中插入了一个新节点(上面示例中的longnames)，新节点的 id 由 LAST_INSERT_ID() 返回 在 MySQL 中，否则你可以以某种方式得到它.
Assuming you've just inserted a new node to your table that holds the actual nodes (longnames in the example above), the id of the new node is returned by LAST_INSERT_ID() in MySQL or else you can get it somehow.
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(), LAST_INSERT_ID(), 0;
这篇关于将数据库结果转化为数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
