SQLアンチパターン 2章 ナイーブツリー（素朴な木）
https://scrapbox.io/files/65fe36fbd8e1880025829690.png
2章 ナイーブツリー（素朴な木）
コメントがスレッド形式が表示され、スレッドはディスカッションのトピックに応じて枝分かれする
各コメントにリプライの対象となっている親コメントを参照させるシンプルな案
問題：リプライの連鎖が長い場合、単一の SQL クエリで取得することが難しくなる
スレッドの深さには制限がないので、あるスレッドの全てのコメントを取得するには、大量のSQLクエリが必要になってしまう・・😇
目的: 階層構造を格納し、クエリを実行する
再起的な関連を持つデータ、ツリー状・階層的な構造で組織化される
ツリー構造
各エントリはノードと呼ばれる
親を持たない最上位のノードは根（ルート）、子を持たない最下部のノードは葉（リーフ）、中間のノードは非葉ノードと呼ばれる
個別のエントリ、部分集合、集合全体を取得するクエリが必要になる
ツリー指向のデータ構造の代表例は2つ
組織図：従業員の上下関係を表す組織図
スレッド形式のコメント欄（掲示板）：あるコメントに対しリプライコメントがぶら下がっていく
アンチパターン: 常に親のみに依存する
parent_id 列を加える方法
同じテーブルにある別の行を親として参照する
思慮が浅く、素朴（ナイーブ）な解決策
このような設計は、隣接リスト（Adjacency List）と呼ばれる
階層的なデータの格納に用いられる最も一般的な設計
code:mermaid
erDiagram
Bugs ||--o{ Comments : has
Comments }o--|| Comments : references
code:sql
CREATE TABLE Comments {
comment_id SERIAL PRIMARY KEY,
parent_id BIGINT UNSIGNED,
bug_id BIGINT UNSIGNED NOT NULL,
author BIGINT UNSIGNED NOT NULL,
comment_date DATETIME NOT NULL,
comment TEXT NOT NULL,
FOREIGN KEY (parent_id) REFERENCES Comments(commend_id),
FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
FOREIGN KEY (author) REFERENCES Accounts(account_id)
}
table: 隣接リストのデータ
comment_id parent_id 発言者 コメント
1 NULL Fran このバグの原因は何かな？
2 1 Ollie NullPointerのせいじゃないかな？
3 2 Fran そうじゃないよ。それは確認済みだ。
4 1 Kukla 無効な入力を調べてみたら？
5 4 Ollie そうか、バグの原因はそれだな。
6 4 Fran よし、じゃあチェック機能を追加してもらえるかな？
7 6 Kukla 了解。修正したよ。
隣接リストへのクエリ実行
隣接リストは、ツリーに関する最も一般的な処理を行うときにアンチパターンになる可能性がある 😭
全ての子孫を取得するクエリで、アンチパターンになる
コメントとその直近の子は、比較的単純なクエリで取得できる
しかし、このクエリが対象にできるのは、ツリー中の2つの階層のみ
code:sql
SELECT c1.*, c2.*
FROM Comments c1 LEFT OUTER JOIN Comments c2
ON c2.parent_id = c1.comment_id;
code:mermaid
graph TD;
ツリーはその性質上、深さに制限が無い場合が多いため、階層の深さに関わらず子孫を取得するクエリを実行できなくてはならない
ノードの数（例：スレッドのコメント数）をCOUNTで数えたり、ノードの値（例：製造業の各部品のコスト）をSUMで合計する場合などは、そべての子孫を対象にする必要がある
隣接リストではこの種のクエリをうまく使えない 😭
アンチパターンの見つけ方
アンチパターンを用いてもよい場合
解決策: 代替ツリーモデルを使用する