Q＆A掲示板を再帰結合ＳＱＬで書く場合テーブル構成はどのような形になるのでしょうか？

Question

実現したいこと

Q＆A掲示板の質問、回答、返信機能をツリー型で完成させたい。

発生している問題・分からないこと

再帰SQLが良いとアドバイス頂いたのですが、使い方が分からず悩んでおります。

該当のソースコード

特になし

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

再帰SQLの説明を読むと、テーブルに一時的に名前を付けることで、再帰処理(ループ)を実現できるということなので、質問と回答を結び付けてそれを繰り返し表示する流れになると思うのですが、質問と回答、返信のテーブルは分けるべきでしょうか？

補足

※質問機能のテーブル構造
id (質問番号)
ts (投稿日時)
text (質問内容)
title (質問タイトル)
name (質問者名)
stamp (リアクションスタンプ)
unique_id (質問UUID)
ip (IPアドレス)
attach1 (アップロードされたファイル)
attach2 (アップロードされたファイル)
attach3 (アップロードされたファイル)
usericon (アップロードされたファイル)

※回答機能で保存予定のコンテンツ
アップロードされたファイル、名前、質問日時

※参考サイト
https://qiita.com/Shoyu_N/items/f1786f99545fa5053b75

https://www.letitride.jp/entry/2020/06/15/161826

※Q＆A掲示板の設計図
https://marvelapp.com/whiteboard/urN35BK2SNIqAd9D5XwO

Comments

[yambejp]

参考テーブルをcreate table+insertで挙げてください
結果はUL-LI形式でほしいのでしょうか？

[utm.]

内容が曖昧すぎませんか？
多分ほとんどの人がLEFT JOINなりで結合すればいいと思うと思うのですけど

[homepage-site]

yambejpさん回答ありがとうございます。
見返してみたところ過去にコードを書いていたようですが、下記のコードで問題のある箇所などありますでしょうか？

CREATE TABLE sortable (
id bigint(20) AUTO_INCREMENT PRIMARY KEY,
parent_id bigint(20) ,
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
text varchar(500) NOT NULL,
title varchar(255) NOT NULL,
name varchar(255) NOT NULL,
stamp INT (11) NOT NULL,
unique_id varchar(36) NOT NULL UNIQUE KEY default uuid(),
ip varchar(39) NOT NULL,
attach1 varchar(255) NOT NULL,
attach2 varchar(255) NOT NULL,
attach3 varchar(255) NOT NULL,
usericon varchar(255) NOT NULL,
FOREIGN KEY (parent_id) REFERENCES sortable(id)
);

WITH RECURSIVE threads AS (
SELECT
id,
parent_id,
ts,
text,
title,
name,
stamp,
unique_id,
ip,
attach1,
attach2,
attach3,
usericon,
id AS top_id,
CAST(id AS CHAR(255)) AS path, /* 1つのデータ型を互換性のある別のデータ型に変換 */
0 AS depth /* 深さが0 ＝ 親（質問）を取得する */
FROM sortable
WHERE parent_id IS NULL

UNION ALL
SELECT
s.id,
s.parent_id,
s.ts,
s.text,
s.title,
s.name,
s.stamp,
s.unique_id,
s.ip,
s.attach1,
s.attach2,
s.attach3,
s.usericon,
t.top_id,
CONCAT(t.path, '-', s.id) AS path,
t.depth + 1 AS depth /* ひたすらに1を＋していく */
FROM sortable s /* SELECT * FROM テーブル名 */
INNER JOIN threads t ON s.parent_id = t.id
)
SELECT * FROM threads
ORDER BY top_id DESC, path

[homepage-site]

utm.さん回答ありがとうございます。
質問の内容が薄すぎました申し訳ありません。

Answer 1

mysqlですとこんな感じに再帰処理を書きます

テーブルとビューの作成

create table messages (
id int(5) unsigned zerofill primary key,
parent_id int null,
content text
);

insert into messages values
(1, NULL, '親1'),
(2, 1, '子1'),
(3, 1, '子2'),
(4, 2, '孫1'),
(5, 4, 'ひ孫1'),
(6, 2, '孫2'),
(7, 2, '孫3'),
(8, 1, '子3'),
(9, 8, '孫4'),
(10, 1, '子4'),
(11, NULL, '親2'),
(12, 11, '子5'),
(13, 12, '孫5');

drop view if exists test_view;
create view test_view  as
with recursive threads as (
    select
        id,
        parent_id,
        content,
        id as top_id,
        cast(id as char(255)) as path,
        0 as depth
    from messages
    where parent_id is null

    union all
    select 
        t1.id,
        t1.parent_id,
        t1.content,
        t2.top_id,
        concat(t2.path, '-', t1.id) as path,
        t2.depth + 1 as depth
    from messages as t1
    inner join threads as t2 on t1.parent_id = t2.id
)
select * from threads

階層表示

select id,concat(repeat("\t",depth),content) as content,path
from test_view
order by path

※idの長さ指定とpathのサイズは調整が必要です

Comments

[homepage-site]

yambejpさん回答ありがとうございます。
コードは別の形になってしまったのですが、参考にさせて頂きました。