Kategória fa lekérdezése 3 szintig egy SQL utasításban

Optimalizálni kellett egy nagyobb kategória fának a lekérdezését, hogy egy SQL utasítással lehessen lekérdezni az összes szükséges információt, így ne legyen annyiszor megszólítva az adatbáziskezelő.

Természetesen már korábban is belefutottam a problémába, de eddig nem publikáltam semmit.

Tehát álljon itt egy SQL utasítás megjegyzésekkel:

--
-- SQL utasítás, amely faszerkezetbe rendezett kategóriákat tud
-- maximum 3 szintig listázni.
-- Az eredményt lineárisan bejárva a programozási nyelvben is lehet 
-- egy több dimenziós tömböt felépíteni, mert nem fog előfordulni,
-- hogy a felsőbb szint esetleg később jönne a listában!
--
SELECT

-- TODO: További oszlopokat szabadon hozzáadni!

-- Az első táblában lévő információkra van szükségünk:
c_1.id

-- Ebben az oszlopban a kategória hierarchiának megfelelően fordított
-- sorrendben kerülnek "," karakterrel összefűzésre
-- Az eredményhalmaz feldolgozása során nagy segítséget nyújthat egy többdimenziós
-- szerkezet összeállításához.
, CONCAT_WS(",", c_1.id, c_2.id, c_3.id) AS category_hierarchy

-- Ez az oszlop azt számolja ki, hogy hányadik kategória szinten 
-- fog elhelyezkedni az adott sorban lévő "c_1"-es kategória
, 1 + LENGTH(CONCAT_WS(",", c_1.id, c_2.id, c_3.id))
  - LENGTH( REPLACE ( CONCAT_WS(",", c_1.id, c_2.id, c_3.id), ",", "") )  AS level_number
-- A listázott kategóriához hozzácsatoljuk, ha lehetséges a szülő kategóriáját,
-- és a szülő kategóriához hozzácsatoljuk annak a szülőjét is, ha lehetséges:
FROM category_tree AS c_1
LEFT JOIN category_tree AS c_2 ON c_1.parent_id = c_2.id
LEFT JOIN category_tree AS c_3 ON c_2.parent_id = c_3.id
WHERE
-- Ez csak apróság, de általában mindenhol jelen van egy "Látható-e a kategória"
-- típusú oszlop információ, ami alapján csak a publikus kategóriák lesznek listázva
-- Természetesen láthatóság nem csak a levél kategóriára lesz ellenőrizve.
-- Ha a szülő nem látható, akkor nem lenne értelme listázni a benne lévő 
-- elemeket
(
c_1.status = 1
-- A 2. és 3. csatolt táblában az IS NULL is szükséges, mert nem biztos
-- hogy minden sorhoz lehetett további szülő és szülő-szülő kategóriát 
-- csatolni. Ekkor ugye a LEFT JOIN miatt ezek a mezők NULL értékúek lesznek.
AND (c_2.status IS NULL OR c_2.status = 1)
AND (c_3.status IS NULL OR c_3.status = 1)
)
-- Ez a feltétel azt köti ki, hogy az 1. vagy a 2. vagy 3. szintnek a gyökérben
-- kell végződnie, amelynek már nincs szülője
AND (
c_1.parent_id IS NULL
OR c_2.parent_id IS NULL
OR c_3.parent_id IS NULL
)
ORDER BY
-- Szükséges növekvő sorban a szint számnak megfelelő sorba rendezni az 
-- eredményeket, hogy az eredményhalmaz lineáris feldolgozása során ne legyen
-- olyan kategória, amelynek a szülője még nem került volna feldolgozásra.
level_number ASC
-- Érdemes valamilyen egyértelmű sorszámozást tartalmazó oszlop alapján 
-- még rendezni az eredményeket:
, c_1.sequence_number ASC

Egyébként a fenti SQL utasítás innen is elérhető, ahol esetleg további finomítások is belekerülnek majd:
https://gist.github.com/racztiborzoltan/99872dc265fa5df25c9199a775d6106a

Ui.: Ideje lenne már a Blogger-be beledobnom a szintakszis kiemelő alkalmatosságot.