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

Question

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

我尝试了不同的存储方式:

使用自连接:

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

使用祖先 gem

等等

但问题是，无论我使用什么，SQL 语句的数量总是线性的。 (1 条语句获取所有根注释，然后 1 条语句用于每个根的子项，然后 1 条语句用于所有子项，等等)

有没有更有效的方法来实现这一点？

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

Answer 1

您可以坚持使用 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。这将为您提供子树中的所有节点兴趣作为评论实例。我过去使用过这样的查询，并且 WITH RECURSIVE 的速度是迭代技术的两倍多，即使是浅树，我猜更深的树会看到更好的加速。

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

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