"Tuyệt Kỹ" Recursive CTE: Lấy Toàn Bộ Quyền HRBAC Chỉ Với 1 Câu SQL Duy Nhất

Chào anh em! Chắc hẳn sau bài viết trước về Hierarchical RBAC (HRBAC), nhiều anh em đã hình dung ra được cái cấu trúc cây phân quyền rồi đúng không?

Nhưng lý thuyết rẽ nhánh là một chuyện, đụng tay vào gõ code lại là chuyện khác. Rất nhiều bạn khi thiết kế bảng role_hierarchy xong thì đực mặt ra: "Chết cha, thằng Admin kế thừa Manager, Manager kế thừa Staff. Giờ user đang là Admin, làm sao lấy được quyền của cả 3 cấp này dưới Database?"

Giải pháp "ngây thơ" nhất của dev mới là: Dùng vòng lặp while hoặc đệ quy trong code (Node.js/PHP), cứ mỗi vòng lặp lại nã một câu SELECT xuống database để tìm role con. Hệ quả? Cây role sâu 10 cấp là bạn nện 10 câu query liên tục. Server DB khóc thét!

Hôm nay, với tư cách là một người từng đập đi xây lại không biết bao nhiêu hệ thống, mình sẽ trao cho anh em một thanh bảo kiếm trong SQL: Recursive CTE (Đệ quy trong cơ sở dữ liệu)

1. Recursive CTE Là Cái Quái Gì?

CTE (Common Table Expression) - Hiểu môn na là một cái bảng tạm thời trên RAM do bạn định nghĩa ra trong lúc chạy query, dùng xong thì vứt (bắt đầu bằng từ khóa WITH)

Còn Recursive CTE (CTE Đệ quy) là một bảng tạm có khả năng... tự gọi lại chính nó cho đến khi không còn dữ liệu thỏa mãn điều kiên nữa thì dừng. Tính năng này có mặt từ PostgreSQL 8.4 và MySQL 8.0, sinh ra chính xác là để giải quyết bài toán truy vấn dữ liệu dạng Cây (Tree) hoặc Đồ thị (Graph) như sơ đồ tổ chức công ty, hoặc cây phân quyền HRBAC của chúng ta.

Nó hoạt động dựa trên 2 phần chính:

1. Base Case (Điểm neo): Lấy ra dữ luệ gốc ban đầu (Ví dụ: Lấy Role hiện tại của User).
2. Recursive Step (Bước đệ quy): Từ dữ liệu gốc, query tiếp để tìm ra các nhánh con, rồi từ nhánh con tìm ra nhánh cháu, cứ thế nối vào kết quả ban đầu bằng UNION ALL

2. Sơ Đồ Database Tóm Tắt

Để anh em dễ theo dõi câu query bên dưới, mình chốt lại cấu trúc bảng liên quan:

• Bảng roles: id, name (Ví dụ: 1 - Admin, 2 - Manager, 3 - Staff)
• Bảng role_hierarchy: parent_role_id, child_role_id (Lưu quan hệ: Admin kế thừa Manager -> parent: 1, child: 2)
• Bảng permissions: id, code (Ví dụ: 'delete_user', 'view_dashboard')
• Bảng role_permissions: role_id, permission_id (Gán quyền cho role)

3. "Câu Query Triệu Đô" Lấy Toàn Bộ Permission

Giả sử User đăng nhập có role Admin (id = 1). Dưới đây là câu SQL duy nhất bạn cần để quét sạch mọi permission của Admin, Manager và Staff

WITH RECURSIVE RoleTree AS (
    -- 1. BASE CASE (Điểm Neo): Lấy Role gốc của User
    SELECT id AS role_id 
    FROM roles 
    WHERE id = 1  -- Thay số 1 bằng Role ID thực tế của User
    
    UNION ALL
    
    -- 2. RECURSIVE STEP (Đệ quy): Tìm tất cả các Role con, cháu, chắt...
    SELECT rh.child_role_id 
    FROM role_hierarchy rh
    INNER JOIN RoleTree rt ON rh.parent_role_id = rt.role_id
)

-- 3. KẾT QUẢ: Lấy danh sách Permission từ tập hợp các Role tìm được ở trên
SELECT DISTINCT p.code
FROM permissions p
JOIN role_permissions rp ON p.id = rp.permission_id
JOIN RoleTree rt ON rp.role_id = rt.role_id;

Mình giải thích luồng chạy một chút cho anh em dễ "thấm":

• Lần đầu tiên, RoleTree chỉ có role_id = 1 (Admin).
• Đoạn đệ quy nhảy vào: Nó join bảng role_hierarchy với RoleTree (đang có id 1). Nó thấy id 1 có thằng con là id 2 (Manager). Thế là nó nhét id 2 vào RoleTree.
• Nó chạy tiếp vòng lặp: Tìm xem thằng id 2 nhét vào có con không? Có, là id 3 (staff). Nó lại nhét id 3 vào bảng RoleTree.
• Nó chạy tiếp: id 3 có con không? Không. Đệ quy dừng lại!
• Kết quả cuối cùng: RoleTree chứa[1, 2, 3]. ĐoạnSELECT DISTINCT ở dưới cùng chỉ việc join vào tập hợp[1, 2, 3] này để lấy ra toàn bộ quyền (dùng DISTINCT để loại bỏ các quyền bị trùng lặp do nhiều role cùng có).

Cực kỳ thanh lịch và tốc độ bàn thờ! mọi gánh nặng tính toán được đẩy xuống hết DB Engine (vốn là trùm xử lý tập hợp), Backend của bạn không cần tốn một giọt mồ hôi nào để vòng lặp hay xử lý array.

4. Lời Khuyên Của Người Đi Trước (Best Practices)

Recursive CTE rất mạnh, nhưng cũng là con dao hai lưỡi:

Chống lặp vô hạn: Nếu data của bạn bị lỗi cấu hình vòng tròn (A là con B, B là con A), CTE sẽ chạy vô hạn và làm nổ RAM database. Mặc định các RDBMS có giới hạn độ sâu (VD: PostgreSQL có max_recursion_depth), nhưng tốt nhất bạn nên chặn việc cấu hình vòng tròn ngay từ code lúc Insert/Update.

Vẫn phải dùng Cache: Dù query này có nhanh đến mấy, thì việc User click vào từng trang bạn lại bắt DB chạy CTE một lần là điều tối kỵ. Hãy dùng query này để lấy quyền LÚC ĐĂNG NHẬP (hoặc lúc Role bị thay đổi), sau đó nhét mảng kết quả vào Redis hoặc JWT Token nhé.

Anh em đã thử dùng CTE bao giờ chưa? Nếu anh em đang code bằng các ORM hiện đại như TypeORM (NestJS), Prisma hay Eloquent (Laravel) và đang thắc mắc làm sao để viết cái cục Raw SQL đệ quy này vào trong ORM cho mượt, có muốn mình làm thêm một bài hướng dẫn cách map nó vào code luôn không?