使用CTE（Common Table Expression）實現延遲加載(Lazy loading)與查詢無限層資料結構

---

---

普遍社群媒體都有提供留言功能，而使用者又能針對特定的留言回覆，產生多層留言。若系統沒有限制則會產生無限層留言的情況。此篇針對無限層的資料結構，紀錄如何查詢並實現延遲載入的功能。

Field Name	Description
comment_id	留言ID
parent_id	父層留言的ID，若為null代表第一層

此結構可以存儲留言及其層級關係，comment_id 用於標識每個留言，parent_id 用於指示該留言是哪個父留言的回覆。

延遲加載由前端實作，後端則負責依序給予對應的資料，只要使用 OFFSET - FETCH 分頁技巧就能依照自己要的順序，給予對應的資料。

e.g.

SQL
 OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY    

表示取從第0筆資料開始的後兩筆資料 = 取第1、2筆資料(可以依據需求更改)。

留言的部分利用建立時間做排序就能依序取得資料解決延遲載入，但遇到的問題是子留言的建立時間並不是照著順序的，有可能第一層最舊的留言會有新的子留言，這時如果只照著建立時間排序，若回傳最新的10筆資料，就會產生前端拿到這則最新的子留言，卻沒拿到原本第一層的父留言，導致畫面出錯的問題。

所以為了解決此問題取資料時的分頁排序還是以第一層的建立時間為基準，取最新10筆的第一層留言，並包含10筆全部的子留言，這樣就不會有子層留言對應不到父層留言的問題。

但也因為可以有無限子留言，要每一層都確實撈取到就要使用CTE。

假設資料表名為comment_history

e.g.

SQL
WITH CommentHierarchy AS (
    -- Anchor member definition
    SELECT
        comment_id,
        parent_id,
        message,
        created_time,
        created_user,
        1 AS Level
    FROM
        comment_history
    WHERE
        parent_id IS NULL -- Fetching top-level comments
		and comment_id in (
  select comment_id from comment_history
  where parent_id is null　
  order by created_time desc 
  OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY)
	

    UNION ALL

    -- Recursive member definition
    SELECT
        c.comment_id,
        c.parent_id,
        c.message,
        c.created_time,
        c.created_user,
        ch.Level + 1 AS Level
    FROM
        comment_history c
    INNER JOIN
        CommentHierarchy ch ON c.parent_id = ch.comment_id
)
SELECT 
    comment_id,
    parent_id,
    message,
    created_time,
    created_user,
	Level
FROM
    CommentHierarchy
ORDER BY
    created_time DESC  

---

語法解釋:

建立 CommentHierarchy ，先取留言的原始資料 where 條件為

parent_id IS NULL (代表為第一層) 

與

comment_id in (
  select comment_id from comment_history
  where parent_id is null　
  order by created_time desc 
  OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY)

配合延遲載入取前10筆最新的留言，並建立新欄位 Level 值為 1 (第一層留言)。

---

有了第一層後，使用原本的資料與 CommentHierarchy JOIN，綁定關係為原本資料的parent_id等於CommentHierarchy的comment_id。(藉由第一層comment_id，取第二層子留言)

-- Recursive member definition
    SELECT
        c.comment_id,
        c.parent_id,
        c.message,
        c.created_time,
        c.created_user,
        ch.Level + 1 AS Level
    FROM
        comment_history c
    INNER JOIN
        CommentHierarchy ch ON c.parent_id = ch.comment_id

持續藉由 CommentHierarchy的comment_id判斷是否有子留言，是使用遞歸的方式取得每層子留言，最後合併所有資料。

以上就能取得父層留言的所有子留言，並標示留言的層數。

comment_id	parent_id	message	created_time	created_user	Level
138808E8-5FFA-4F26-927D-3E06654079BC	82D25141-29D3-4135-A162-A588B61AA6EF	2233	2023-07-25 11:55:44.443	Alvin	2
0D46466C-3725-4FBA-9676-C1D364ED0D16	82D25141-29D3-4135-A162-A588B61AA6EF	1112	2023-07-25 11:16:55.977	OKA	2
548439B1-7D6C-4268-88CA-A52570DC5409	30C7A6F8-4E69-4F3C-836B-04AEBAE46F84	test	2023-07-25 11:10:43.610	David	3
30C7A6F8-4E69-4F3C-836B-04AEBAE46F84	82D25141-29D3-4135-A162-A588B61AA6EF	XD	2023-07-25 11:10:17.560	Alvin	2
82D25141-29D3-4135-A162-A588B61AA6EF	NULL	so tired	2023-07-25 11:09:52.187	OKA	1
6722E047-5547-4EE7-8959-938A10534858	B6C20E72-2F7A-4891-B8A3-301DBBF72CC6	cool~	2023-07-25 11:02:23.717	Alvin	2
2281BDB4-7B21-442F-84C5-A0736BE7DD9A	B6C20E72-2F7A-4891-B8A3-301DBBF72CC6	wow new system	2023-07-25 11:02:19.607	Alvin	2
B6C20E72-2F7A-4891-B8A3-301DBBF72CC6	NULL	what is this?	2023-07-25 11:02:09.060	JSON	1

SQL

---

---