Recursive CTE ile Hiyerarşik Veri Sorgulamak

Veritabanında çalışan-yönetici ilişkisi, ürün kategorileri veya organizasyon şeması gibi hiyerarşik veriler sık karşılaşılan bir durum. Bu tür yapıları sorgulamak için Recursive CTE (Common Table Expression) kullanmak en pratik yol.

Recursive CTE, kendini referans alan bir sorgulamadır. Bir başlangıç noktasından (anchor member) başlayıp, katman katman aşağıya inerek tüm ilişkili kayıtları bulur. Örneğin, bir çalışanın tüm astlarını veya bir kategorinin tüm alt kategorilerini bulmak için ideal.

Örnek olarak, çalışan ve yönetici ilişkisini içeren bir tablo düşün:

1CREATE TABLE calisanlar (
2id INT PRIMARY KEY,
3ad VARCHAR(100),
4yonetici_id INT,
5FOREIGN KEY (yonetici_id) REFERENCES calisanlar(id)
6);
7
8INSERT INTO calisanlar VALUES
9(1, 'Ahmet Müdür', NULL),
10(2, 'Zeynep Şef', 1),
11(3, 'Fatih Uzman', 2),
12(4, 'Elif Uzman', 2),
13(5, 'Mert Asistan', 3);

Şimdi Ahmet'in (id=1) altında kaç seviye çalışan olduğunu ve hiyerarşiyi görmek istiyoruz:

1WITH RECURSIVE calisanlar_hiyerarsisi AS (
2-- Anchor member: Başlangıç noktası
3SELECT id, ad, yonetici_id, 1 AS seviye
4FROM calisanlar
5WHERE yonetici_id IS NULL
6
7UNION ALL
8
9-- Recursive member: Katman katman aşağıya inerek devam et
10SELECT c.id, c.ad, c.yonetici_id, ch.seviye + 1
11FROM calisanlar c
12INNER JOIN calisanlar_hiyerarsisi ch ON c.yonetici_id = ch.id
13)
14SELECT id, ad, seviye, REPEAT(' ', seviye - 1) || ad AS hiyerarsi
15FROM calisanlar_hiyerarsisi
16ORDER BY seviye, ad;

Beklenen çıktı:

1id | ad | seviye | hiyerarsi
21 | Ahmet Müdür | 1 | Ahmet Müdür
32 | Zeynep Şef | 2 | Zeynep Şef
43 | Fatih Uzman | 3 | Fatih Uzman
55 | Mert Asistan | 4 | Mert Asistan
64 | Elif Uzman | 3 | Elif Uzman

Kodu adım adım açarsak: Anchor member (ilk SELECT) hiçbir yöneticisi olmayan kaydı bulur. Recursive member ise, CTE'nin kendisine katılarak her iterasyonda bir seviye daha aşağıya iner. UNION ALL ile her seviyenin sonuçları birleştirilir.

Sık yapılan hata, MAX RECURSION DEPTH ayarını unutmaktır. Derinlik çok fazlaysa sonsuz döngüye girebilir. MySQL'de `SELECT @@cte_max_recursion_depth;` ile kontrol et ve gerekirse `SET cte_max_recursion_depth = 1000;` ile artır. PostgreSQL ve SQL Server'da benzer ayarlar vardır.

Recursive CTE, ağaç yapısındaki verileri döngüyle sorgulamaktan çok daha temiz ve performanslı. Organizasyon şeması, dosya sistemi hiyerarşisi, ürün kategorileri, hepsi bu yöntemle kolayca çıkar.