将数据库结果转换为数组 [英] Turn database result into array
问题描述
我刚刚制作了用于组织查询层次数据的关闭表"方式的更新/添加/删除部分，该方式在本幻灯片共享的第70页上显示:
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.
这就是我想找回的东西:
Here's what I would like to get back:
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
Update2: 我现在可以为每个类别添加深度，如果有帮助的话.
Update2 : I was able to add depths to each of the categories now, if that can be of any help.
推荐答案
好吧，我已经编写了PHP类，这些类扩展了Zend Framework DB表，行和行集类.无论如何，我一直在进行开发，因为我在关于分层结构的两周内在 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孵化器，而我的演示文稿是具有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.
行和行集还定义了称为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由MySQL中的LAST_INSERT_ID()返回，否则您可以获取它不知何故.
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屋！
