วิธีทำ Recursive SELECT Query ใน MySQL (Hierarchical Data)

MySQL 8.0+ รองรับ Recursive CTE (Common Table Expression) สำหรับ Query ข้อมูลแบบ Hierarchical/Tree (Recursive Query) รุ่นเก่าไม่รองรับ ต้องใช้ Stored Procedure หรือ Application Layer

MySQL 8.0+: Recursive CTE (แนะนำ)

ตัวอย่าง: Employee Hierarchy

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

INSERT INTO employees VALUES
(1, 'CEO', NULL),
(2, 'VP Engineering', 1),
(3, 'VP Sales', 1),
(4, 'Senior Dev', 2),
(5, 'Junior Dev', 4),
(6, 'Sales Rep', 3);

Recursive CTE: หา Subordinates ทั้งหมดของ VP Engineering (id=2)

WITH RECURSIVE subordinates AS (
    -- Anchor: เริ่มจาก VP Engineering
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE id = 2
    
    UNION ALL
    
    -- Recursive: หาลูกน้องระดับต่อไป
    SELECT e.id, e.name, e.manager_id, s.level + 1
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

ผลลัพธ์

+----+--------------+------------+-------+
| id | name         | manager_id | level |
+----+--------------+------------+-------+
| 2  | VP Engineering| 1         | 0     |
| 4  | Senior Dev   | 2         | 1     |
| 5  | Junior Dev   | 4         | 2     |
+----+--------------+------------+-------+

MySQL 5.7 และก่อน: Workarounds

1. Stored Procedure (Loop)

DELIMITER //
CREATE PROCEDURE GetSubordinates(IN root_id INT)
BEGIN
    CREATE TEMPORARY TABLE tmp (id INT, name VARCHAR(100), level INT);
    INSERT INTO tmp SELECT id, name, 0 FROM employees WHERE id = root_id;
    
    REPEAT
        INSERT INTO tmp
        SELECT e.id, e.name, t.level + 1
        FROM employees e
        INNER JOIN tmp t ON e.manager_id = t.id
        WHERE e.id NOT IN (SELECT id FROM tmp);
    UNTIL ROW_COUNT() = 0 END REPEAT;
    
    SELECT * FROM tmp;
    DROP TEMPORARY TABLE tmp;
END //
DELIMITER ;

CALL GetSubordinates(2);

2. Adjacency List + Application Layer

Query ทั้งตารางมา แล้วสร้าง Tree ใน Application (PHP, Python, Node.js)

3. Nested Set Model (Schema Design แบบเก่า)

ALTER TABLE employees ADD COLUMN lft INT, ADD COLUMN rgt INT;
-- ใช้ Left/Right Values แทน Parent ID
-- Query Subtree: WHERE lft >= parent_lft AND rgt <= parent_rgt

4. Closure Table (แนะนำสำหรับ Version เก่า)

CREATE TABLE hierarchy (
    ancestor INT,
    descendant INT,
    depth INT,
    PRIMARY KEY (ancestor, descendant)
);
-- เก็บทุก Path: (1,1,0), (1,2,1), (1,4,2), (1,5,3), (2,2,0), (2,4,1), (2,5,2)...
-- Query: SELECT descendant FROM hierarchy WHERE ancestor = 2;

ตัวอย่างจากบทความ (Session Variable Hack - ไม่แนะนำ)

SELECT var1.id as id, @sessionName:= var1.Name as NameofStudent
FROM (SELECT * FROM tblSelectDemo ORDER BY id DESC) var1
JOIN (SELECT @sessionName:= 4) tmp
WHERE var1.id = @sessionName;

⚠️ วิธีนี้ Deprecated, Unreliable, ไม่ใช้ใน Production

สรุป

• ✅ MySQL 8.0+: ใช้ WITH RECURSIVE CTE (Standard, Readable, Performant)
• ⚠️ MySQL 5.7: ใช้ Closure Table (Schema Design) หรือ Stored Procedure
• ❌ หลีกเลี่ยง Session Variable Hack
• 📝 Design Schema ให้รองรับ Hierarchical Query ตั้งแต่แรก (Closure Table ดีที่สุด)