查询树的项数 [英] Querying a count of items of a tree
问题描述
`parent` int（11）
COMMENT \'Node`s parent（nid）.\'，
PRIMARY KEY（` nid`）
）
对于给定的节点ID，我想得到一个它的所有后代。但是：
从 node_list中选择COUNT（*）， parent =？
仅返回直系子女的数量。
对于具有最大最大层数的层次结构，您可以使用级联联接执行单个查询以查找记录计数。如果层数大于三或四，这可能并不漂亮，但是应该可以。
选择count（ *）来自node_list n1的
n1上的
外连接node_list n2.parent = n1.nid $ 3，parent上的
外连接node_list n3上n3.parent = n2.nid
外连接node_list n4上n4.parent = n3.nid
...依此类推，您可以根据需要选择多个级别。
在现实世界中，大多数层次结构系统的深度实际上是相当有限的。即使它们在理论上是无限的。例如，站点菜单可能允许无限制级别的结构，但是超过三或四则很难使用。是否对嵌套施加限制取决于您，但这可能会使事情变得更容易。
但是，如果您确实有一个开放式层次结构，而不知道它会走多深，或者如果上面的查询太慢，那么您将需要一个循环。该循环是在MySQL存储过程中还是在PHP中都是无关紧要的；您将需要一种或另一种循环。当然，不必担心您担心的循环循环。
I将使用递归PHP函数来实现。也许是这样的：
function countDescendants（$ db，$ nid）{
$ total = 0;
$ query =从节点中选择nid，其中parent =。（int）$ nid;
$ res = $ db-> query（$ query）;
foreach（$ res作为$ data）{
$ total + = countDescendants（$ db，$ data [’nid’]）;
}
$ total + = $ res-> num_rows;
返回$ total；
}
然后，您可以调用它并用单行代码即可得到答案：
$ number_of_descendants = countDescendants（$ starting_nid）;
一个相当简单的递归函数（我假设您正在使用 mysqli 为您的数据库，并且您已经将连接排序以传递给函数）。
如果有真正巨大的层次结构，或者您要查询很多次，它可能会变慢一些，但是有一些方法可以通过改进我提供的这个基本示例来加快它的速度。例如，您可以使用准备好的语句查询，并使用不同的nid值填充同一条语句：这将节省大量的数据库工作。但是，为了在较小的层次结构上简单使用，上面的代码应该没问题。
这些技术中的任何一个最大的缺陷是节点结构中是否存在循环-即具有自己的后代之一作为其父ID的节点。这种情况将导致上面的PHP代码无限循环，并且在嵌套连接SQL查询的情况下，将导致记录计数严重偏斜。无论哪种情况，如果您的系统都可能出现这种情况，则需要针对它进行编码。但这确实使事情复杂化，所以我在这里不再赘述。
希望有帮助。
（注意：以上代码未经测试：我直接输入答案而不运行它；如果有错字，我们深表歉意）
Let's say I have a table such as this:
CREATE TABLE IF NOT EXISTS `node_list` (
`nid` int(11) NOT NULL AUTO_INCREMENT,
`parent` int(11)
COMMENT \'Node`s parent (nid).\',
PRIMARY KEY (`nid`)
)
For a given node id, I want to get a count of all of its descendants. However:
SELECT COUNT(*) FROM `node_list` WHERE `parent`=?
Only returns the count of immediate children. What might a good way of doing this without a mess of for loops look like?
For a hierarchy with a known maximum number of tiers, you can do a single query with a cascade of joins to find the record count. If the number of tiers is greater than three or four, this might not be pretty, but it should work.
select count(*)
from node_list n1
outer join node_list n2 on n2.parent = n1.nid
outer join node_list n3 on n3.parent = n2.nid
outer join node_list n4 on n4.parent = n3.nid
...and so on for as many levels as you need. Try not to make it too many though, or the performance may suffer.
In the real world, most hierarchy systems are actually fairly limited in their depth; even if they are theoretically unlimited. For example, a site menu may allow unlimited levels of structure, but beyond three or four it gets hard to use. It's up to you whether you impose a limit on the nesting, but it may make things easier.
However, if you do have an open-ended hierarchy where you don't know how deep it could go, or if the above query is too slow, then you are going to need a loop. Whether that loop is in a MySQL stored procedure or whether it's in PHP is immaterial; you'll need a loop one way or the other. It certainly doesn't need to be the mess of for loops you're worried about, though.
I would do it with a recursive PHP function. Maybe something like this:
function countDescendants($db, $nid) {
$total = 0;
$query = "select nid from Nodes where parent = ".(int)$nid;
$res = $db->query($query);
foreach($res as $data) {
$total += countDescendants($db, $data['nid']);
}
$total += $res->num_rows;
return $total;
}
Then you can call it and get your answer with a single line of code:
$number_of_descendants = countDescendants($starting_nid);
A fairly simple recursive function (I've made an assumption that you're using mysqli for your DB and you've got your connection already sorted to pass into the function).
Granted, if you have a really huge hierarchy or you're querying it lots of times, it may get a bit slow, but there are ways of speeding it up by improving on this basic example I've given. For example, you could use a prepared statement query, and just populate the same statement with different nid values: this would save a large part of the DB work. But for simple usage on a small hierarchy, the above code should be fine.
The one big pitfall with any of these techniques is if you have a loop in your node structure -- ie a node having one of its own descendants as it's parent ID. This scenario will cause an infinite loop with the above PHP code, and will cause the record count to be badly skewed in the case of the nested joins SQL query. In either event, if it is possible for your system to have this situation, you will need to code against it. But that does complicate things, so I won't go into it here.
Hope that helps.
(NB: above code not tested: I typed it straight into the answer without running it; apologies if there's any typos)
这篇关于查询树的项数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
