関連記事
CTE（Common Table Expressions）とは
CTE（共通テーブル式）には再帰的なCTEと再帰的ではないCTEがあります。
-
再帰的なCTEは深い階層の処理をする場合に、クエリ内で一時的に使えるテーブルのことです。 -
再帰的ではないCTEはサブクエリを分かりやすく書き換えるために使用します。
次のようなテーブルを例に説明します。
CREATE TABLE videos (
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(100),
PRIMARY KEY (id)
);
CREATE TABLE comments (
id INT NOT NULL AUTO_INCREMENT,
video_id INT,
comment VARCHAR(200),
parent_id INT,
PRIMARY KEY (id),
FOREIGN KEY (video_id) REFERENCES videos(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
INSERT INTO videos (title) VALUES
('世界最大級のグミを1人で食う！'),
('スライムで風呂作ってみた'),
('メントスで槍を作ってコーラにブッさす');
INSERT INTO comments (video_id, comment, parent_id) VALUES
(1, 'ついつい見に来てしまうのは何故だろうか', NULL),
(1, '硬いグミを食うだけで１億再生行く男', NULL),
(3, 'メントスコーラよりマヨネーズみたいなの気になった', NULL),
(1, 'xxxxx', 2), -- '硬いグミを食うだけで１億再生行く男' へのコメント
(1, 'しかも3分', 2), -- '硬いグミを食うだけで１億再生行く男' へのコメント
(1, '通報がありました。コメントを削除してください。', 4); -- 'xxxxx'へのコメント
SELECT * FROM videos;
SELECT * FROM comments;
-- +----+--------------------------------------------------------+
-- | id | title |
-- +----+--------------------------------------------------------+
-- | 1 | 世界最大級のグミを1人で食う！ |
-- | 2 | スライムで風呂作ってみた |
-- | 3 | メントスで槍を作ってコーラにブッさす |
-- +----+--------------------------------------------------------+
-- +----+----------+--------------------------------------------------+-----------+
-- | id | video_id | comment | parent_id |
-- +----+----------+--------------------------------------------------+-----------+
-- | 1 | 1 | ついつい見に来てしまうのは何故だろうか | NULL |
-- | 2 | 1 | 硬いグミを食うだけで１億再生行く男 | NULL |
-- | 3 | 3 | メントスコーラよりマヨネーズみたいなの気になった | NULL |
-- | 4 | 1 | xxxxx | 2 |
-- | 5 | 1 | しかも3分 | 2 |
-- | 6 | 1 | 通報がありました。コメントを削除してください。 | 4 |
-- +----+----------+--------------------------------------------------+-----------+
硬いグミを食うだけで１億再生行く男 に関するコメントだけ取得する場合、次のようになります。
SELECT * FROM comments WHERE parent_id = 2
UNION ALL
SELECT
comments.*
FROM
comments JOIN (
SELECT * FROM comments WHERE parent_id = 2
) AS t
ON
comments.parent_id = t.id;
-- +----+----------+------------------------------------+-----------+
-- | id | video_id | comment | parent_id |
-- +----+----------+------------------------------------+-----------+
-- | 4 | 1 | xxxxx | 2 |
-- | 5 | 1 | しかも3分 | 2 |
-- | 6 | 1 | 通報がありました。コメントを削除してください。 | 4 |
-- +----+----------+------------------------------------+-----------+
再帰的でないCTE
再帰的ではないCTEを使用して最下層の通報がありました。コメントを削除してください。だけ取得します。
SELECT
comments.*
FROM
comments JOIN (
SELECT * FROM comments WHERE parent_id = 2
) AS t
ON
comments.parent_id = t.id;
-- 上下同じ結果が取得できます。
-- 下は`再帰的ではないCTE`で、サブクエリを`WITH T` とすることで可読性を高めます。
WITH t AS (
SELECT * FROM comments WHERE parent_id = 2
)
SELECT
comments.*
FROM
comments JOIN t
ON
comments.parent_id = t.id;
再帰的なCTE
硬いグミを食うだけで１億再生行く男 に関するコメントの取得ですが、最初の例では通報がありました。コメントを削除してください。 に対してコメントがあった場合、さらに下の階層になるため取得することができません。
そこで、どれだけコメントがネストされていったとしても再帰的なCTEで取得することができます。
-- WITH RECURSIVE(再帰) CTE名 とします。
WITH RECURSIVE t AS (
-- 最初に実行される処理
SELECT * FROM comments WHERE parent_id = 2
UNION ALL
-- 最初の（階層の）処理をCTE（T）として、次の処理が実行されます。
-- そして次の（階層の）の処理をCTE（T）としてまた次の階層の処理というふうに、最下層まで繰り返されます。
SELECT
comments.*
FROM
comments JOIN t
ON
comments.parent_id = t.id
)
SELECT * FROM t;
-- +------+----------+-----------------------------------------------+-----------+
-- | id | video_id | comment | parent_id |
-- +------+----------+-----------------------------------------------+-----------+
-- | 4 | 1 | xxxxx | 2 |
-- | 5 | 1 | しかも3分 | 2 |
-- | 6 | 1 | 通報がありました。コメントを削除してください。 | 4 |
-- +------+----------+-----------------------------------------------+-----------+
このように再帰的なCTEを使用することでどれだけコメントが増えてもコードを書き換えることなく取得することができます。