RailsでのMySQL8.0を使用した、再帰クエリの書き方

Question

前提・実現したいこと

お世話になります。
Railsで掲示板を作っています。
スコア順に並び替えたコメントと、それに対するスコア順に並び替えた返信をツリー状に表示したいと考えています。
n-1
n-1-1
n-1-1
n-2
n-2-1
n-3

イメージとしては上記のような、Redditのような形です。
調べてみたところ、ツリー構造の実装には

隣接リストモデル
経路列挙モデル
入れ子集合モデル
閉包テーブルモデル

などがあり、再帰クエリを使えるのなら、ツリー構造には隣接リストが最も適していると
リンク内容
こちらに書いてあり、またSQL文で書いたほうが高速だと
リンク内容
こちらにあったので、MySQLを8.0にアップデートし再帰クエリを用いようと試みました。
しかし初心者のため、SQL文に慣れておらず、またRails内でのSQL文の書き方がわからなかったため、とりあえずRailsの機能で再帰クエリを使おうと、下記のようなコードを書いてみました。

topics_controller.rb
@post = @topic.posts.includes(:user).where(parent_post_id: 0).descendents.sort_by{|post| -post.score}

post.rb 
  has_many :children, foreign_key: :parent_post_id, class_name: 'Post'
  
  def descendents
    self.map do |p|
      p.children.map do |child|
        [child] + child.descendents
      end.flatten
    end
  end
  

しかしundefined methodのエラーが出てしまいました。
includesを外してみたり、sort_byを外してみたりしたのですが、問題は解決しませんでした。
初心者のため、何か根本的な勘違いをしているのだと思うのですが、それがどこなのかがわかりません。
できればMySQLを用いた再帰クエリの書き方を、MySQLでは難しいのであれば、Railsでの再帰クエリの書き方をご教授いただければ幸いです。またその他の解決法もお待ちしております。

該当のデータ

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | bigint       | NO   | PRI | NULL    | auto_increment |
| name           | varchar(191) | NO   |     |         |                |
| content        | text         | YES  |     | NULL    |                |
| post_image     | varchar(191) | YES  |     | NULL    |                |
| parent_post_id | int          | NO   |     | 0       |                |
| upvotes        | int          | YES  |     | 0       |                |
| downvotes      | int          | YES  |     | 0       |                |
| user_id        | bigint       | NO   | MUL | NULL    |                |
| topic_id       | bigint       | NO   | MUL | NULL    |                |
| created_at     | datetime     | NO   |     | NULL    |                |
| updated_at     | datetime     | NO   |     | NULL    |                |
| score          | int          | NO   |     | 0       |                |
+----------------+--------------+------+-----+---------+----------------+

Post id, name, content, parent_post_id, score
1,"A","隣接リスト最高！",0,2
2,"B","シンプルでいいですよね",1,0
3,"C","アンチパターンですよ、経路列挙モデル使いましょう",1,2
4,"D","入れ子集合モデルの方が良いですよ",3,1
5,"E","こんにちは",0,0

###望む結果
topic
|_ 隣接リスト最高！　2点
| |_ アンチパターンですよ、経路列挙モデル使いましょう　2点
| |_ 入れ子集合モデルの方が良いですよ　1点
| |_ シンプルでいいですよね　0点
|_ こんにちは　0点

発生している問題・エラーメッセージ

undefined method `descendents' for #<Post::ActiveRecord_AssociationRelation:0x00...

補足情報（FW/ツールのバージョンなど）

mysql Ver 8.0.20 for Linux on x86_64 (MySQL Community Server - GPL)
Rails 6.0.3.2

試したこと

ここに問題に対して試したことを記載してください。

補足情報（FW/ツールのバージョンなど）

ここにより詳細な情報を記載してください。

Comments

[sazi]

> MySQLを用いた再帰クエリの書き方
テーブルの定義情報と、サンプルデータ、求める結果を追記して下さい。

[tttkkm]

開発環境に問題があり、返信遅れてしまい申し訳ありません。
追記いたしました。このような感じでよろしいでしょうか？不足な点などあればお知らせください。

Answer 1

scoreで降順ソートした深さ優先探索で出力順は再現できるでしょう。
SQLだけで実現するのであれば、MySQL 8.0の再帰With句のサンプル | CodeZine の深さ優先探索コードがまるっとそのまま使えます。
表示上のレイアウトはレベル数値からrails側で構築したほうが良いでしょうね。

with recursive tmp as (
    select
        id
        ,parent_post_id
        ,score
        ,lpad(row_number() over(order by score desc),4,'0') as rn
    from post
),q1(id,parent_post_id,lv,score,sortstr) as (
    select
        id
        ,parent_post_id
        ,1 lv
        ,score
        ,cast(rn as char(255))
    from tmp
    where parent_post_id = 0
    union all
    select
        p.id
        ,p.parent_post_id
        ,q.lv+1
        ,p.score
        ,concat(q.sortstr,',',p.rn)
    from q1 q
    join tmp p
        on q.id = p.parent_post_id
)
select 
    po.id
    , po.name
    , po.content
    , po.score
    , tbl1.lv
from q1 tbl1
join post po
    on po.id = tbl1.id
order by sortstr
;

ただしこの方法はツリーが深くなっていくと破綻しますので(sortstrの文字数に限界がある)、rails側で木構造を再現したほうが安全なんじゃないかなとは思います。

Comments

[tttkkm]

回答ありがとうございます！
ツリーが深くなっていくと破綻するというのは、SQL全般そうなのでしょうか？
もしそうであれば、Rails側で木構造を再現する方法をご教授いただければ幸いです。

[hope_mucci]

この方法だとソートに使っているsortstrのサイズが最大255桁で、枝が増えるたびにIDの桁数+1桁増えていくので、これを超えるような深さになるとソートできずに破綻します。sortstrをselectしてみると分かります。
rails側でこのソートを再現するには、parent_post_id=0のノードをrootとする深さ優先探索をしていくだけです。深さ優先探索の方法は検索すればいっぱい出てきます。そちらを参考にしてください。

[tttkkm]

ありがとうございます！なるほどsortstrの問題なんですね。ググってやってみます！