在 postgres 中存储和检索任意深度嵌套结构的有效方法是什么？

发布于 2024-12-25 07:46:00 字数 462 浏览 2 评论 0原文

在我的 ruby-on-rails 应用程序中，我嵌套了可以嵌套任意长度的注释。

我尝试了不同的存储方法：

使用自连接：

belongs_to :parent, :class_name => 'Comment', :foreign_key => 'parent_id'
has_many :children, :class_name => 'Comment', :foreign_key => "parent_id"

使用ancestry gem

等

但问题是，无论我使用什么，总会有线性数量的 SQL 语句。（1 个语句来获取所有根注释，然后为每个根的子级使用 1 个语句，然后为该根的所有子级使用 1 个语句，等等）

是否有更有效的方法来实现此目的？

Postgres 9.1，但希望向后兼容的解决方案是首选。

原文

In my ruby-on-rails app, I have nested comments that can be nested an arbitrary length.

I tried different ways of storing this:

Using self joins:

belongs_to :parent, :class_name => 'Comment', :foreign_key => 'parent_id'
has_many :children, :class_name => 'Comment', :foreign_key => "parent_id"

Using ancestry gem

etc

The problem, though, is that no matter what I use, there will always be an linear number of SQL statements. (1 statement to grab all the root comments, and then 1 statement for each root's children, and then 1 statement for all the children of that, etc)

Is there a more efficient way to accomplish this?

Postgres 9.1, but hopefully backwards compatible solutions are preferred.

分享到QQ

分享到微博

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

发布评论

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

骄兵必败 2025-01-01 07:46:00

您可以坚持使用 parent_id 指针列并使用 find_by_sql 和 WITH RECURSIVE 查询并让数据库一次性完成所有工作。像这样的东西：

comments = Comment.find_by_sql(%Q{
    with recursive tree(id) as (
        select c.id, c.column1, ...
        from comments c
        where c.id in (#{roots.join(',')})
        union all
        select c.id, c.column1, ...
        from comments c
        join tree on c.parent_id = tree.id
    )
    select id, column1, ...
    from tree
})

其中 roots 是一个 Ruby 数组，其中包含您感兴趣的根节点的 id。这将为您提供子树中的所有节点作为 Comment 实例感兴趣。我过去使用过这样的查询，即使对于浅树，WITH RECURSIVE 的速度也比迭代技术快两倍以上，我猜更深的树会看到更好的加速。

您使用的 parent_id 结构对于大多数事情来说都非常方便，并且与 ActiveRecord 的工作方式非常吻合。此外，坚持当前的结构意味着您可以不理会应用程序的其余部分。

WITH RECURSIVE 在 PostgreSQL 8.4 及更高版本中可用。

You could stick with your parent_id pointer column and use find_by_sql and a WITH RECURSIVE query and let the database do all the work in one shot. Something like this:

comments = Comment.find_by_sql(%Q{
    with recursive tree(id) as (
        select c.id, c.column1, ...
        from comments c
        where c.id in (#{roots.join(',')})
        union all
        select c.id, c.column1, ...
        from comments c
        join tree on c.parent_id = tree.id
    )
    select id, column1, ...
    from tree
})

where roots would be a Ruby array holding the ids of the root nodes that you're interested in. That will give you all the nodes in the subtrees of interest as Comment instances. I've used queries like this in the past and WITH RECURSIVE was well over twice as fast as your iterative technique even with shallow trees, I'd guess that deeper trees would see even better speed ups.

The parent_id structure you're using is very convenient for most things and meshes quite well with how ActiveRecord wants to work. Also, sticking with your current structure means that you can leave the rest of your application alone.

WITH RECURSIVE is available in PostgreSQL 8.4 and higher.

回复收藏 0 原文

温柔女人霸气范 2025-01-01 07:46:00

看看awesome_nested_set，我想你会喜欢的。

https://github.com/collectiveidea/awesome_nested_set

回复收藏 0 原文

婴鹅 2025-01-01 07:46:00

有许多文章讨论经典（即基于基本 SQL）选项。例如，这是对经典选项，这篇文章介绍了如何使用递归查询带导轨。

但是，由于您使用的是 Postgressql（如果未启动），您可以选择使用 ltree 扩展，它创建专门适合树结构的索引。我本来会使用 ltree_hierarchy 但事实证明 postgres 不支持 ltree 标签中的破折号与我的 id 使用 uuid 不兼容。我选择了 ancestry gem，尽管它没有使用好的功能（2021 年唯一真正的选择是< a href="https://github.com/collectiveidea/awesome_nested_set" rel="nofollow noreferrer">awesome_nested_set 其中有一些关于如何“为您的 postgresql 列创建一个 text_pattern_ops 索引。”