Recursive SQL ( CTE )

8 min readFeb 22, 2023

有一個需求需要階級制度的設計，之前都是採用 Closure table 的做法
但這次想嘗試使用 Parent–Child ( Adjacency List Model )架構，為了可以拿到所有下屬以及全部下屬的下屬 …and so on，於是找到了關鍵字 Common Table Expression.

備註：目前有四種方式可以完成 Hierarchies 需求的 DB Schema，要看你的需求是新刪修需求多，還是查詢多來決定使用哪一種，有興趣可看這篇

Common Table Expression (CTE)

A common table expression (CTE) is a named temporary result set that exists within the scope of a single statement and that can be referred to later within that statement, possibly multiple times. The following discussion describes how to write statements that use CTEs.
From https://dev.mysql.com/doc/refman/8.0/en/with.html

我的理解有點類似在 function 中宣告區域變數的概念，可以被重複使用很多次、好閱讀以及 Query 結束就不見…等，最重要的是解決了我的需求

而 CTE 最小支援 mysql 8.0 以上

從最簡單的

假設這邊有個 Table 叫 roles，先拿到全部的資料

簡單的畫了 Tree 版本的角色階級圖如下面這樣

延伸閱讀：MySQL Integer 0 vs NULL

首先來找到最大的（這邊可以改成任意節點，打個比方拿到 4 以下的..）

再來是找 parent_id 等於 1 的下屬

然後再找 parent_id 是 2、3、4 的，於是到這邊就可以感覺要用 Recursive

以圖來說我希望下面這兩行可以合併，於是腦袋有了 UNION 的思維

接著開始實作 CTE，先從最簡單的

WITH your_name AS (
 SELECT id, name FROM roles
)
SELECT * FROM your_name

上面透過 WITH 來開頭，接著宣告一個變數叫 your_name 並透過 AS 給予值

最下面再使用 select 來搭配使用 your_name 這個變數，可以想成是宣告參數並且使用

進階一點來個重複使用 your_name 這參數

WITH your_name AS (
 SELECT id, name FROM roles
)
SELECT * FROM your_name UNION ALL 
SELECT * FROM your_name WHERE id = 1 ORDER BY id = 1 DESC

自行定義 CTE 的 column name

WITH your_name (new_column1, new_column2 ) AS (
 SELECT id, name FROM roles
)
SELECT * FROM your_name UNION ALL 
SELECT * FROM your_name WHERE new_column1 = 1 ORDER BY new_column1 = 1 DESC

所以基於以上範例，可以得出語法糖如下

WITH 開頭後加上參數名稱（ cte_name ）接著 optional 的 column_name，最後再補上 AS 跟之後的 subquery

// Formula
WITH cte_name (column1, column2....) AS
(
   // subquery here
)
SELECT (column1, column2....) FROM cte_name;

介紹完比較簡單的部分之後接著是 RECURSIVE 部分，這邊會修改 subquery

首先思路是先取得最高的權限，然後再把最高權限的 id 變成是下一次 query 的 parent_id 然後 so on…，先上結果圖

以此次例子，假設我只想拿到 4 以下的話

只需要改動 where 的部分

開始解釋語法糖部分，一開始先拿 initial row 的值（假設是 N ），然後會再把 N 帶到 recursive part 當作是初始值

然後 recursive part，這邊需要燒腦下為什麼要用 join 的，如果不使用 join 的話，它其實會一直遞迴 initial part 的資料，這並不是我們預期的，而且還會因此出現 cte_max_recursion_depth 的 error

所以在 recursive part 這邊會讓新來的 id ( 以此次 case 是 cte_id ) 跟 roles 的 parent_id 兩者劃上等號，也就是下方這行，只要搞懂最核心的這行剩下的都很好理解惹

JOIN temp ON temp.cte_id = roles.parent_id

以下是 error 示意圖，所以在上圖有加上 Limit 來限制

經朋友補充決定畫上分解圖，還有補充一下結束的機制是在也沒有資料了

確認過接著就寫 code

RawMethods…

Laravel 8 目前沒支援 cte，所以只能使用 RawMethods 系列…

DB::raw(........)

經過一番努力最終會大概長這樣

但是因應需求 id 的部分會是變數，總不可能需求都是從最高級別為主的吧，所以這邊要是動態的參數，於是採用 string literal 的方式

看起來很完美，但這樣可能會遇到 SQL injection attacks

補充：只要用到 RawMethods 之類的比如 whereRaw, selectRaw 的都有機會會遇到 SQL injection（官方文獻），以下提供 3 種方式來防範

1. 採用 Query Binding，其底層原理就是把參數給 escaped and quoted 來防止

2. 加上 validation 來防範

3. 使用 addslashes

如果要用套件的話可以使用 laravel-cte

以上是 CTE 的相關研究過程，官方有不少範例，如有更進階的需求可以自行研究…

Recursive SQL ( CTE )

Common Table Expression (CTE)

從最簡單的

RawMethods…

Ref.

Written by Stephen Chen