ruby-on-rails - 如何在 rails 中创建一个递归函数，它将返回 parent 的所有 child ，以及所有 child 的 child 等等......到 N 级

Question

我有一个方法如图所示:

def all_pages_and_its_n_level_child

     @parent_pages = Page.where(:parent_id => params[:page_id])
     #this will give me all child of this page
     for page in @parent_pages
          child_exists=Page.where(:parent_id=>page.id)
          #this will give all children of that page
          #*I want to make this loop further so that i can check to N-levels,
          #this is only for two levels*
     end


end  

Answer 1

在现代 RDB 上，递归 CTE 可用于处理递归结构。

class Page < ApplicationRecord; end

Page.find_by_sql(
  "WITH RECURSIVE r AS (
     #{Page.where(id: 2).to_sql}
     UNION ALL
     #{Page.joins('JOIN r').where('r.id = pages.parent_id').to_sql})
   SELECT * FROM r")

#  Page Load (0.7ms)  WITH RECURSIVE r AS (
#     SELECT `pages`.* FROM `pages` WHERE `pages`.`id` = 2
#     UNION ALL
#     SELECT `pages`.* FROM `pages` JOIN r WHERE (r.id = pages.parent_id))                                                              
#   SELECT * FROM r
#    => [#<Page id: 2, parent_id: 1, created_at: "2018-08-21 15:00:43", updated_at: "2018-08-21 15:00:43">, #<Page id: 3, parent_id: 2, created_at: "2018-08-21 15:00:50", updated_at: "2018-08-21 15:00:50">]

据我所知，mysql、postgres、sqlite3 支持递归 CTE。

编辑@2020/12/17
在 postgresql 上，您需要具备以下条件:

Page.find_by_sql(
  "WITH RECURSIVE r AS (
     #{Page.where(id: 2).to_sql}
   UNION ALL
     #{Page.joins('JOIN r ON r.id = pages.parent_id').to_sql})
   SELECT * FROM r")

(感谢@Dan 指出)