質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.34%

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

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 365

tttkkm

score 9

前提・実現したいこと

お世話になります。
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/ツールのバージョンなど)

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • sazi

    2020/06/20 12:49

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

    キャンセル

  • tttkkm

    2020/06/21 11:44

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

    キャンセル

回答 1

checkベストアンサー

+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側で木構造を再現したほうが安全なんじゃないかなとは思います。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2020/06/21 18:21

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

    キャンセル

  • 2020/06/21 21:18

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

    キャンセル

  • 2020/06/22 11:35

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.34%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る