有没有一种简单的方法来查询节点的子节点? [英] Is there a simple way to query the children of a node?

问题描述

我一直在使用 嵌套集模型 最近.我喜欢为几乎所有有用的操作和视图设计查询.我坚持的一件事是如何选择节点的直接子节点(并且仅子节点，而不是进一步的后代！).

I've been using the crap out of the Nested Set Model lately. I have enjoyed designing queries for just about every useful operation and view. One thing I'm stuck on is how to select the immediate children (and only the children, not further descendants!) of a node.

老实说，我确实知道一种方法 - 但它涉及无法管理的 SQL 数量.我相信有一个更直接的解决方案.

To be honest, I do know of a way - but it involves unmanageable amounts of SQL. I'm sure there is a more straightforward solution.

推荐答案

您是否阅读了您发布的文章?它位于查找节点的直接从属"标题下

Did you read the article you posted? It's under the heading "Find the Immediate Subordinates of a Node"

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
    nested_category AS parent,
    nested_category AS sub_parent,
    (
        SELECT node.name, (COUNT(parent.name) - 1) AS depth
        FROM nested_category AS node,
        nested_category AS parent
        WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.name = 'PORTABLE ELECTRONICS'
        GROUP BY node.name
        ORDER BY node.lft
    )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
    AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
    AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1
ORDER BY node.lft;

然而，我所做的(这是作弊)是我将嵌套集与邻接列表结合起来——我在表中嵌入了一个parent_id"，这样我就可以轻松地查询节点的子节点.

However, what I do (this is cheating) is I combined the nested set with adjacency lists -- I embed a "parent_id" in the table, so I can easily ask for the children of a node.

这篇关于有没有一种简单的方法来查询节点的子节点?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！