如何有效地检索树中节点的路径(与帖子“将平面表解析为树?”相关)

发布于 2024-08-01 22:24:25 字数 513 浏览 4 评论 0 原文

这个问题是这篇文章的后续问题:

将平面表解析为树的最有效/优雅的方法是什么?

我喜欢 ClosureMap 解决方案,但我还有一个问题需要解决。

如何轻松检索树中特定节点的路径? 例如,如果您查看提供的树:

ID Node Name

1 'Node 1'
2 '节点 1.1'
3 '节点 2'
4 '节点 1.1.1'
5 '节点 2.1'
6 'Node 1.2'

到 1.1.1 的路径将是:

ID = 1, 2, 4

在不进行递归 SQL 调用的情况下,是否有一种优雅的方法来检索路径?

This question is a follow up to this post:

What is the most efficient/elegant way to parse a flat table into a tree?

I liked the ClosureMap solution, but I have an additional problem to solve.

How can you easily retrieve the path to a particular node in a tree? For example, if you look at the tree provided:

ID Node Name

1 'Node 1'
2 'Node 1.1'
3 'Node 2'
4 'Node 1.1.1'
5 'Node 2.1'
6 'Node 1.2'

The path to 1.1.1 would be:

ID = 1, 2, 4

Without doing recursive SQL calls, is there an elegant way to retrieve a path?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

满意归宿 2024-08-08 22:24:25
SELECT ancestor_id
FROM ClosureTable
WHERE descendant_id = 4;

返回值 1、2、4。但是它们在单独的行上返回,并且没有表明它们的顺序正确(我们可能不假设数字顺序对应于树层次结构顺序)。

有时,您还会将每个路径的深度存储在 ClosureTable 中。 但即使没有,您也可以计算给定节点有多少个祖先,并使用它进行排序:

SELECT ct1.ancestor_id, COUNT(*) AS depth
FROM ClosureTable ct1
 JOIN ClosureTable ct2 ON (ct1.ancestor_id = ct2.descendant_id)
WHERE ct1.descendant_id = 4
GROUP BY ct1.ancestor_id
ORDER BY depth;

是的,这仍然返回三行结果。 如果您使用 MySQL,则可以访问 GROUP_CONCAT()。 否则,很容易获取三行并在应用程序代码中连接它们的值。

SELECT ancestor_id
FROM ClosureTable
WHERE descendant_id = 4;

Returns the values 1, 2, 4. But they are returned on separate rows, and they give no indication that they're in the right order (we might not assume numerical order corresponds to tree hierarchy order).

Sometimes you would also store the depth for every path in the ClosureTable. But even if not, you can count how many ancestors a given node has, and use that to sort:

SELECT ct1.ancestor_id, COUNT(*) AS depth
FROM ClosureTable ct1
 JOIN ClosureTable ct2 ON (ct1.ancestor_id = ct2.descendant_id)
WHERE ct1.descendant_id = 4
GROUP BY ct1.ancestor_id
ORDER BY depth;

Yes, this still returns the result in three rows. If you use MySQL, you have access to GROUP_CONCAT(). Otherwise it's easy to fetch three rows and concatenate their values in application code.

ぶ宁プ宁ぶ 2024-08-08 22:24:25

我想我找到了最好的解决方案,但并没有真正意识到它就在我提到的帖子中:)。

Site Point 有一篇很好的文章,展示了如何使用左/右属性检索特定节点:

http://www.sitepoint.com/article/hierarchical-data-database/2/

I think I found the best solution, and didn't really realize it's in the post I referred to :).

Site Point had a nice article that shows the how to retrieve a specific node using the Left/Right attributes:

http://www.sitepoint.com/article/hierarchical-data-database/2/

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文