Ví dụ về CTE đệ quy để làm việc với hệ thống phân cấp

Hãy tưởng tượng: bạn có một shop online với hàng ngàn sản phẩm, được sắp xếp gọn gàng vào các kệ — danh mục, danh mục con, danh mục con của con. Trên web thì nhìn như menu xổ xuống đẹp mắt, nhưng trong database thì đúng là đau đầu luôn. Làm sao để lấy cả nhánh "Điện tử → Điện thoại thông minh → Phụ kiện" chỉ với một truy vấn? Làm sao đếm được mỗi danh mục có bao nhiêu cấp lồng nhau? JOIN bình thường chịu thua rồi — phải dùng đệ quy thôi!

Xây dựng cấu trúc danh mục sản phẩm bằng CTE đệ quy

Một trong những bài toán kinh điển trong cơ sở dữ liệu quan hệ là xử lý cấu trúc phân cấp. Hãy tưởng tượng bạn có một cây danh mục sản phẩm: danh mục chính, danh mục con, danh mục con của con, v.v. Ví dụ:

Điện tử
  └── Điện thoại thông minh
      └── Phụ kiện
  └── Laptop
      └── Gaming
  └── Ảnh và video

Cấu trúc này dễ hình dung trên giao diện shop online, nhưng lưu và truy xuất nó trong database thì sao? Đây là lúc CTE đệ quy phát huy tác dụng!

Bảng danh mục gốc

Đầu tiên, tạo bảng categories để lưu thông tin về các danh mục sản phẩm:

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,       -- Định danh duy nhất cho danh mục
    category_name TEXT NOT NULL,          -- Tên danh mục
    parent_category_id INT                -- Danh mục cha (NULL cho danh mục gốc)
);

Ví dụ dữ liệu sẽ thêm vào bảng:

INSERT INTO categories (category_name, parent_category_id) VALUES
    ('Điện tử', NULL),
    ('Điện thoại thông minh', 1),
    ('Phụ kiện', 2),
    ('Laptop', 1),
    ('Gaming', 4),
    ('Ảnh và video', 1);

Ở đây có gì:

• Điện tử — là danh mục gốc (không có cha, parent_category_id = NULL).
• Điện thoại thông minh nằm trong danh mục Điện tử.
• Phụ kiện thuộc về Điện thoại thông minh.
• Các danh mục còn lại cũng tương tự.

Cấu trúc dữ liệu hiện tại trong bảng categories như sau:

Xây dựng cây danh mục bằng CTE đệ quy

Bây giờ mình muốn lấy toàn bộ hệ thống phân cấp danh mục kèm cấp độ lồng nhau. Dùng CTE đệ quy nhé.

WITH RECURSIVE category_tree AS (
    -- Truy vấn gốc: chọn tất cả danh mục gốc (parent_category_id = NULL)
    SELECT
        category_id,
        category_name,
        parent_category_id,
        1 AS depth -- Cấp độ lồng nhau đầu tiên
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    -- Truy vấn đệ quy: tìm danh mục con cho từng danh mục
    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.depth + 1 AS depth -- Tăng cấp độ lồng nhau
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)
-- Truy vấn cuối: lấy kết quả từ CTE
SELECT
    category_id,
    category_name,
    parent_category_id,
    depth
FROM category_tree
ORDER BY depth, parent_category_id, category_id;

Kết quả:

Có gì ở đây?

1. Đầu tiên truy vấn gốc (SELECT … FROM categories WHERE parent_category_id IS NULL) chọn các danh mục chính. Ở đây chỉ có Điện tử với depth = 1.
2. Sau đó truy vấn đệ quy dùng INNER JOIN để thêm danh mục con, tăng cấp độ lồng nhau (depth + 1).
3. Quá trình này lặp lại cho đến khi tìm hết tất cả danh mục con ở mọi cấp.

Một số cải tiến hữu ích

Ví dụ cơ bản này chạy ổn, nhưng thực tế thì thường cần nhiều hơn. Giả sử bạn muốn làm breadcrumb cho web hoặc muốn cho manager biết danh mục nào có nhiều nhánh con nhất. Cùng xem vài cải tiến thực tế cho truy vấn này nhé.

1. Thêm đường dẫn đầy đủ của danh mục

Đôi khi cần hiển thị đường dẫn đầy đủ tới danh mục, ví dụ: Điện tử > Điện thoại thông minh > Phụ kiện. Có thể làm bằng cách nối chuỗi:

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        category_name,
        parent_category_id,
        category_name AS full_path,
        1 AS depth
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.full_path || ' > ' || c.category_name AS full_path, -- nối chuỗi
        ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)

SELECT
    category_id,
    category_name,
    parent_category_id,
    full_path,
    depth
FROM category_tree
ORDER BY depth, parent_category_id, category_id;

Kết quả:

Bây giờ mỗi danh mục đều có đường dẫn đầy đủ thể hiện cấp độ lồng nhau.

2. Đếm số lượng danh mục con

Nếu muốn biết mỗi danh mục có bao nhiêu danh mục con thì sao?

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        parent_category_id
    FROM categories

    UNION ALL

    SELECT
        c.category_id,
        c.parent_category_id
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)

SELECT
    parent_category_id,
    COUNT(*) AS subcategory_count
FROM category_tree
WHERE parent_category_id IS NOT NULL
GROUP BY parent_category_id
ORDER BY parent_category_id;

Kết quả:

Bảng này cho thấy Điện tử có 3 danh mục con (Điện thoại thông minh, Laptop, Ảnh và video), còn Điện thoại thông minh và Laptop thì mỗi cái có 1.

Lưu ý và lỗi thường gặp khi dùng CTE đệ quy

Đệ quy vô hạn: Nếu dữ liệu có vòng lặp (ví dụ một danh mục trỏ về chính nó), truy vấn có thể chạy mãi không dừng. Để tránh, hãy giới hạn độ sâu bằng WHERE depth < N hoặc dùng LIMIT.

Tối ưu hiệu năng: CTE đệ quy có thể chậm nếu dữ liệu lớn. Hãy tạo index cho parent_category_id để tăng tốc.

Lỗi UNION thay vì UNION ALL: Luôn dùng UNION ALL cho CTE đệ quy, nếu không PostgreSQL sẽ cố loại bỏ bản ghi trùng, làm truy vấn chậm đi.

Ví dụ này cho thấy CTE đệ quy giúp xử lý cấu trúc phân cấp cực tiện. Kỹ năng lấy hệ thống phân cấp từ database sẽ cực hữu ích cho bạn trong nhiều dự án thực tế, như xây menu web, phân tích cấu trúc tổ chức hoặc làm việc với graph. Giờ thì bạn đã sẵn sàng giải quyết mọi bài toán phức tạp rồi đó!

1

Khảo sát/đố vui

, cấp độ , bài học

Không có sẵn

Giới thiệu về CTE

Giới thiệu về CTE

Mở