Autor Zpráva
Karel.K
Profil *
Rád bych poprosil o radu, jak inteligentně zapsat jeden SQL dotaz.

Mám tabulku, ve které si držím hierarchicky uspořádané kategore, se sloupci
id_kategorie, nazev_kategorie, id_nadrazene_kategorie

Nejvyšší kategoerie má id_nadrazene_kategorie rovné NULL.

A já bych potřeboval ze znalosti id kategorie získat posloupnost např. Živočichové > Strunatci > Savci > Šelmy > Kočkovití, přičemž ale dopředu nevím, jak dlouhá ta posloupnost bude.
Leo
Profil
http://interval.cz/clanky/metody-ukladani-stromovych-dat-v-relacnich-d atabazich/

Leo
Karel.K
Profil *
Díky, jestli jsem to pochopil správně, je řešením prokousat se těmi úrovněmi while cyklem, což znamená tolik databázových dotazů, kolik je vrstev. Mám obavu, zda to bude dost efektivní.

Zapsat to jedním chytrým dotazem asi nepůjde, co?

Napadlo mě několik takyřešení, budu rád za vaše názory:

1. Na http://dev.mysql.com/tech-resources/articles/hierarchical-data.html je ukázka dotazu pro výpis cesty, pokud znám její délku, tedy počet úrovní (jejich příklad pro délku 4):
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4

FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS' AND t4.name = 'FLASH';

Co kdybych napsal dotaz sjednocující cestu délky 1 a 2 a 3 a ... a 10 (pokud budu mít jistotu, že 10 je maximální dálka)?

2. Načíst si jedním dotazem z db všechny řádky tabulky a zpracovat si to v php sám (i když to asi taky moc efektivní nebude, že)?

3. Do tabulky přidat sloupec typu VARCHAR a v něm si pro každý řádek onen řetězec "Živočichové > Strunatci > Savci > Šelmy > Kočkovití" držet rovnou předgenerovaný (imho nejlepší, co mě napadlo)?
Leo
Profil
Relacni databaze na to proste neni stavena, a bud mate redundanci dat, spoustu selectu, nebo komplikovane vkladani. Osobne se priklanim k tomu, ze kazda takova vec se musi kesovat na ruznych uronich, takze pokud v te hierarchii nedelate sto zmen denne, vic selektu se nikde neprojevi. Leo
Joker
Profil
Tak šlo by načíst jedním dotazem všechno do nějakého pole a potom zpracovávat to pole. V některých případech by to myslím bylo výkonnější, v jiných to bude akorát přesun zátěže z databáze na PHP.

Ještě mě napadlo další řešení, řešit ten vztah zvláštní tabulkou, něco jako:
vnoreni id_rodice - id_potomka - uroven

kde by byla celá cesta (tj. pokud by třeba kategorie 5 měla cestu 1->3->4->5, bylo by tam: 5-1-1, 5-3-2, 5-4-3)
a pak by se to dalo udělat:
SELECT id_rodice FROM vnoreni WHERE id_potomka=x ORDER BY uroven

Nevýhoda (resp. jak kdy) je, že různí potomci by mohli mít různé pořadí těch kategorií.
Další řešení by bylo přiřadit "natvrdo" každé kategorii úroveň, pokud je vždycky stejná.
Acci
Profil
2. Načíst si jedním dotazem z db všechny řádky tabulky a zpracovat si to v php sám (i když to asi taky moc efektivní nebude, že)?
Řekl bych, že tohle asi bude nejlepší způsob, který sám používám. Navíc, čas provádění PHP bude oprati času získání dat z DB mnohem menší.
ronnie
Profil
Tak. Řešil jsem kategorie dnem i nocí asi týden, na internetu jsem našel množství řešení, zkoušel jsem traverzování kolem stromu, metodu, kterou popisuješ výše a nic jsem nenašel až na jednu metodu, dle mého názoru nejlepší.

V databázi máš jeden sloupec, který uchovává zanoření celé sekce, např.
000300020001. Takový zápis znamená, že nejvyšší rodič (hlavní položka) má kód 0003, další rodič 0002 a vlastní kód potomka je 0001.

Struktora menu může vypadat např. takto:

Počítače
- Notebooky
- PDA
- Počítačové sestavy
PC komponenty
- grafické karty
- - AGP sběrnice
- - PCI-E sběrnice

a ve sloupečku s kódem bude

0001
- 00010001
- 00010002
- 00010003
0002
- 00020001
-- 000200010001
-- 000200010002

atd.

Práce s menu je pak jednoduchá

1) výpis indexových položek
SELECT * FROM category WHERE LENGTH(zanoreni) = 4 ORDER BY zanoreni

2) výpis celé struktury
SELECT * FROM category ORDER BY zanoreni

3) vypis subsekce
- dělam na dva dotazy: nejprve se vybere z URL sekce daný kód, ten pak rozkouskuji a vyberu celou podsekci

SELECT zanoreni FROM category WHERE url = $url
-- vybere např. 000100020003, pak je dotaz takový
SELECT * FROM category WHERE zanoreni IN(0001,00010002) OR zanoreni LIKE '000100020003%'

4) drobečková navigace
- je to podobné jako předchozí, jen tam není podmínka s LIKE

5) mazaní dat
DELETE FROM category WHERE zanoreni LIKE '0003%'

6) vkládání dat
- je třeba vybrat zanoření rodiče a pak poslat dotaz, zda existuje nějaký potomek. Pokud ne, pak má potomek kód rodič + 0001, pokud ano, tak se zvětší kód o + 1 (a přitom se testuje, zda je zvětšení ještě možné). Aby se zachovaly nuly na začátku, používám funkci str_pad, která nuly dovyplní.

7) editace, moveUp a moveDown
- to je trochu složitější, ale jde to:)

Nevýhoda téhle metody je taková, že má "omezenou" možnost zanoření, proto se hodí pouze pro správu menu. Slovo omezenou dávám do závorky, protože v příkladě popsaném výše může mít menu tolik zanoření, kolik dovoluje datový typ sloupce zanoření v tabulce. Např. u varcharu to bude 64, což je mnohem víc, než je třeba, u eshopu je vždycky max. 4 zanoření.

Pak je ještě omezen počet potomků jedné položky. V případě popsaném výše může mít jedna položka nejvyšší 4 místné číslo, což je tedy 9999 možných položek. Běžný eshop má max. 30 podkategorií u jedné položky.

A jak je to s rychlostí? Důležité je používat cachování, ale ve správně navržené aplikaci to není problém. Když se poprvé vygeneruje indexové menu, tak ho serializované uložím do souboru na neomezeně dlouhou dobu. Pokud proběhne nějaký edit, insert nebo delete, tak se cache znehodnotí a při dalším požadavku se vygeneruje znova.

Kromě toho mám ještě Smarty, které necachuje každou stránku zvláště, šablony mají velmi dlouhou trvanlivost, takže je to opravdu velmi rychlé.

Na závěr bych ještě zmínil, že mnoho frameworků má třídu Tree, která má za úkol práci se stromovými strukturami.

Např. v Zend Frameworku je aktuální návrh na Zend_Tree, třídu, která by podobnou práci velmi zjednodušila. Zatím je jen mezi návrhy, takže pokud uspěje, bude v core za cca 2 měsíce.
ronnie
Profil
Ještě jsem mohl dodat, že se popisované struktuře říká "genealogické stromy"
tiso
Profil
ronnie sranda, niečo podobné som "vymyslel" aj ja - vychádzal som z nodov (uzlov), len som dopĺňal aj bodky na oddeľovanie nodov (01.02.03.), ešte som zbytočne použil stĺpec so zanorením, a stĺpec parent_node, že sú navyše mi došlo až neskôr - pri podobnej diskusii tu na JPW. Tvoje riešenie je elegantnejšie...
pmasarik
Profil
Ja som raz našiel johnove riešenie a odvtedy ho bez problémov používam.
Je to jednoduché a stačí jeden dotaz na DB aby sme vybrali celú stromovú štruktúru.

http://www.sitepoint.com/forums/showthread.php?p=1548097
Joker
Profil
ronnie
Bych řekl, že tohle řešení je podobné tomu co jsem psal já, akorát já to mám jako řádky a sloupce ve zvláštní tabulce...
tiso
Profil
pmasarik a čo keď chceš vybrať iba časť?
pmasarik
Profil
tiso hral som sa s tým už dávno a samotná funkcia je na moju hlavu dosť zložitá, ale prišiel som na to ako to urobiť a nebolo to vôbec zložité. Teraz by som musel preštudovať ako to funguje a to sa mi nechce :-) je sobota...
anonym
Profil *
ja pouzivam The Nested Set Model z http://www.vbmysql.com/articles/database-design/managing-hierarchical- data-in-mysql/.. ale mam problem s razenim nejsem stavu prijit na to jak docilit serazenych kategorii
Toto téma je uzamčeno. Odpověď nelze zaslat.

0