Przykład rekursywnych CTE do pracy z hierarchiami

Wyobraź sobie: masz sklep internetowy z tysiącami produktów, wszystko ładnie poukładane na półkach — kategorie, podkategorie, pod-podkategorie. Na stronie wygląda to jak fajne rozwijane menu, ale w bazie danych to już niezły ból głowy. Jak wyciągnąć całą gałąź "Elektronika → Smartfony → Akcesoria" jednym zapytaniem? Jak policzyć, ile poziomów zagnieżdżenia ma każda kategoria? Zwykłe JOIN-y tu nie pomogą — tu wchodzi rekursja!

Budowanie struktury kategorii produktów za pomocą rekursywnych CTE

Jedno z klasycznych zadań w relacyjnych bazach danych — praca z hierarchicznymi strukturami. Wyobraź sobie, że masz drzewo kategorii produktów: główne kategorie, podkategorie, pod-podkategorie i tak dalej. Na przykład:

Elektronika
  └── Smartfony
      └── Akcesoria
  └── Laptopy
      └── Gaimerskie
  └── Foto i wideo

Ta struktura łatwo się prezentuje w interfejsach sklepów internetowych, ale jak ją przechowywać w bazie i wyciągać? Tu z pomocą przychodzą rekursywne CTE!

Początkowa tabela kategorii

Najpierw stworzymy tabelę categories, która będzie trzymać dane o kategoriach produktów:

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,       -- Unikalny identyfikator kategorii
    category_name TEXT NOT NULL,          -- Nazwa kategorii
    parent_category_id INT                -- Kategoria nadrzędna (NULL dla głównych kategorii)
);

Oto przykładowe dane, które wrzucimy do tabeli:

INSERT INTO categories (category_name, parent_category_id) VALUES
    ('Elektronika', NULL),
    ('Smartfony', 1),
    ('Akcesoria', 2),
    ('Laptopy', 1),
    ('Gaimerskie', 4),
    ('Foto i wideo', 1);

Co tu się dzieje:

• Elektronika — to główna kategoria (nie ma rodzica, parent_category_id = NULL).
• Smartfony są w kategorii Elektronika.
• Akcesoria należą do kategorii Smartfony.
• Podobnie reszta kategorii.

Aktualna struktura danych w tabeli categories wygląda tak:

Budowanie drzewa kategorii za pomocą rekursywnego CTE

Teraz chcemy dostać całą hierarchię kategorii z poziomem zagnieżdżenia. Do tego użyjemy rekursywnego CTE.

WITH RECURSIVE category_tree AS (
    -- Bazowe zapytanie: wybieramy wszystkie kategorie główne (parent_category_id = NULL)
    SELECT
        category_id,
        category_name,
        parent_category_id,
        1 AS depth -- Pierwszy poziom zagnieżdżenia
    FROM categories
    WHERE parent_category_id IS NULL

    UNION ALL

    -- Zapytanie rekursywne: znajdujemy podkategorie dla każdej kategorii
    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.depth + 1 AS depth -- Zwiększamy poziom zagnieżdżenia
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id
)
-- Końcowe zapytanie: wyciągamy wyniki z CTE
SELECT
    category_id,
    category_name,
    parent_category_id,
    depth
FROM category_tree
ORDER BY depth, parent_category_id, category_id;

Wynik:

Co tu się dzieje?

1. Najpierw bazowe zapytanie (SELECT … FROM categories WHERE parent_category_id IS NULL) wybiera główne kategorie. W tym przypadku to tylko Elektronika z depth = 1.
2. Potem zapytanie rekursywne przez INNER JOIN dokłada podkategorie, zwiększając poziom zagnieżdżenia (depth + 1).
3. Proces powtarza się, aż znajdą się wszystkie podkategorie na wszystkich poziomach.

Przydatne usprawnienia

Podstawowy przykład działa, ale w realnych projektach często potrzeba więcej. Załóżmy, że robisz breadcrumbs na stronę albo chcesz pokazać menadżerowi, w której kategorii jest najwięcej podkategorii. Zobaczmy kilka praktycznych ulepszeń naszego zapytania.

1. Dodanie pełnej ścieżki kategorii

Czasem warto pokazać pełną ścieżkę do kategorii, np.: Elektronika > Smartfony > Akcesoria. Da się to zrobić przez agregację tekstową:

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, -- sklejamy teksty
        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;

Wynik:

Teraz każda kategoria ma pełną ścieżkę pokazującą zagnieżdżenie.

2. Liczba podkategorii

A co jeśli chcesz wiedzieć, ile podkategorii ma każda kategoria?

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;

Wynik:

W tabeli widać, że Elektronika ma 3 podkategorie (Smartfony, Laptopy, Foto i wideo), a Smartfony i Laptopy po jednej.

Specyfika i typowe błędy przy pracy z rekursywnymi CTE

Nieskończona rekursja: Jeśli dane mają cykle (np. kategoria wskazuje sama na siebie), zapytanie może wpaść w nieskończoną pętlę. Żeby tego uniknąć, można ograniczyć głębokość rekursji przez WHERE depth < N albo limity.

Optymalizacja działania: Rekursywne CTE mogą być wolne na dużych ilościach danych. Stosuj indeksy na parent_category_id żeby przyspieszyć zapytania.

Błąd UNION zamiast UNION ALL: Zawsze używaj UNION ALL w rekursywnych CTE, bo inaczej PostgreSQL będzie próbował usuwać duplikaty, co spowalnia zapytanie.

Ten przykład pokazuje, jak rekursywne CTE pomagają ogarniać hierarchiczne struktury. Umiejętność wyciągania hierarchii z bazy przyda ci się w wielu realnych projektach. Na przykład przy budowie menu strony, analizie struktur organizacyjnych czy pracy z grafami. Teraz jesteś gotowy na zadania dowolnej trudności.

1

Ankieta/quiz

Wprowadzenie do CTE, poziom 27, lekcja 4

Niedostępny

Wprowadzenie do CTE

Wprowadzenie do CTE

Otwórz