[PHP/MYSQL] categoriehierarchie met condities

[Verwijderd]

DOEL: een hiërarchie van categorieën weergeven zoals hieronder. echter alleen categorieën die aan een conditie voldoen (bevat item van gebruiker).

TABELLEN MYSQL:

INGREDIENTEN:
Als uitgangspositie geldt de user_id (in dit voorbeeld 'johan').
1. 'user' Johan heeft 'items' 1,2,3,4,5 en 6.
2. in tabel 2 zie je dat deze items aan verschillende categorieën zijn verbonden (30,42,45,48,50).
3. in tabel 3 zie je dat elke categorie een 'parent_id' heeft, parent '0' betekent dat het de root categorie is.
4. wanneer een (root/parent)categorie een subcategorie bevat, bevat het geen items. bij subcategorieën is een ongelimiteerde diepte mogelijk, het kan dus ook een level 5 subsubsubsubsub zijn.

---

PROBLEEM / VRAAG

Hoe krijg ik een categoriehiërarchie voor de pagina van 'johan', dus alleen van die categorieën waarin zich items van 'johan' bevinden?

Dit lukt me maar niet. geen probleem om een de hiërarchie van ALLE categorieën op te zetten, maar in mijn hoofd 'zie' ik gewoon niet waar ik de gebruiker (johan) als conditie aan moet geven (lijkt mij als eerste) of hoe ik de uitkomst kan 'samenvoegen' tot 1 categoriestructuur ipv allemaal losse categoriepaden per item (zie voorbeeld hieronder).

Er klopt dus iets niet in mijn denkwijze; er moet van achter naar voren gewerkt worden bij het ophalen lijkt me (gebruiker => subsub => sub => parent) om vervolgens de categorieën van voren naar achter weer te geven (parent => sub => subsub).

Wie kan me helpen om hiervoor een goed weg te vinden of om het licht te zien? wat o wat doe of denk ik fout? mijn dank alvast voor een ieder die zich hierover zou willen buigen..

---

een van de voorbeelden van hoe het mij vooral NIET lukt (uiteindelijk moet de uitkomst in een <li>structuur komen, maar dat laten we hier even weg):

PHP:

function getCatHierarchy($parentCat)
{
    $rval = "";
    $res2 = mysql_query("SELECT * FROM categorieen WHERE cat_id='".$parentCat."'");
    while ( $row2 = mysql_fetch_array($res2) )
    {
        $rval = " > ".$row2["cat_naam"].$rval;
        $parentCat = $row2["parent_id"];
        mysql_free_result($res2);
        $res2 = mysql_query("SELECT * FROM categorieen WHERE cat_id=".$parentCat." ORDER BY TITLE");
    }
    mysql_free_result($res2);
    $rval = substr($rval, 2);
    return ($rval);
}


$res = mysql_query("SELECT DISTINCT I.cat_id AS CID FROM user U, items I WHERE U.user_id = 'johan' AND I.item_id = U.item_id ORDER BY I.cat_id");
$array = array();
while($row = mysql_fetch_assoc($res)){
    $array[] = $row['CID'];
}
foreach ($array as $cat) {
    echo getCatHierarchy($cat);
}

Dit resulteert in een categoriepad per item:
rootcat(10) > subcat(20) > subsubcat(30)
rootcat(10) > subcat(20) > subsubcat(30)
rootcat(10) > subcat(20) > subsubcat(42)
rootcat(12) > subcat(37) > subsubcat(45)
rootcat(12) > subcat(37) > subsubcat(48)
rootcat(10) > subcat(21) > subsubcat(50)

terwijl het samengevoegd zou moeten zijn, zoiets als:
rootcat(10) > subcat(20) > subsubcat(30)
................................... > subsubcat(42)
................ > subcat(21) > subsubcat(50)
rootcat(12) > subcat(37) > subsubcat(45)
................................... > subsubcat(48)

[Janoz]

Als je perse deze database structuur aan wilt houden (en geen beperking wilt leggen op de diepte van je boom) kun je beter het idee loslaten dat je dit volledig in sql wilt doen. Een mogelijke opzet zou kunnen zijn:

- Lees uit categorieen alle items die bij een gebruiker horen en maakt alvast items aan (je krijgt nu 30, 42, 45,48,50)
- Bepaal welke parents je nog mist, haal deze op en hangt ze aan de eerder gemaakte items (je krijgt nu 20,21,37)
- Je herhaald vorige stap totdat je geen parents meer mist

En nu heb je je hele menu structuur. Als gebruikers lang blijven hangen op de site en de boom is redelijk diep, dan is het handig om deze in de sessie te bewaren. Je zult echter een afweging tussen snelheid en geheugengebruik moeten maken.

[Verwijderd]

dank voor je reactie Janoz. ik ga even stap voor stap reageren op wat je zegt..

Als je perse deze database structuur aan wilt houden (en geen beperking wilt leggen op de diepte van je boom) kun je beter het idee loslaten dat je dit volledig in sql wilt doen.

zoals vaker het antwoord zal zijn, maakt dit voorbeeld onderdeel uit van een bestaand geheel, dus ligt de dbase structuur inderdaad vast.

- Lees uit categorieen alle items die bij een gebruiker horen en maakt alvast items aan (je krijgt nu 30, 42, 45,48,50)

oke, en dat is precies wat $res doet, $cat geeft de uitkomsten die je aangeeft. dus het begin is goed begrijp ik

- Bepaal welke parents je nog mist, haal deze op en hangt ze aan de eerder gemaakte items (je krijgt nu 20,21,37)

helaas gaat het hier dan al mis met het begrijpen: 'bepaal welke parents je nog mist'.
dan moet ik toch die key in een loop gooien? maar hoe bepaal ik dat subsubcat 42 dezelfde parent heeft als subsubcat30 en dat een foreach of while (over de $cat) dus hier geen uitkomst hoeft te tonen omdat deze uitkomst al is opgehaald (ID:20)? beetje kromme zin, ik hoop dat je begrijpt wat ik bedoel. ik vermoed dat dit de positie is waar de denkfout zit mijnerzijds.

- Je herhaald vorige stap totdat je geen parents meer mist

en vanwege deze omschrijving ga ik er van uit dat het dus wel die foreach of while loop moet zijn.

[Janoz]

Ja, je zult zeker lusjes nodig hebben. Maar programmeren is meer dan lusjes alleen. Het is niet het maar achter elkaar zetten van wat commando's. het is het uitdenken van een algoritme en dit algoritme vervolgens implementeren.

De hoofd categorieen kun je, door joins te gebruiken, gewoon in 1 query ophalen. De items zul je vervolgens moeten onthouden. Het handigste is om hiervoor een map (wat een php array eigenlijk is) te gebruiken waarbij je de ID als key gebruikt. Hierdoor kun je erg makkelijk een item terug vinden. Ook kun je een lijst bijhouden met alle cat id's die je nog mist. Zolang immers de parent id niet 0 is en de id ook nog niet voorkomt in de map met items mis je deze nog.

[CyBeRSPiN]

Met een Oracle DB is zoiets veel simpeler op te lossen omdat deze hierarchische queries ondersteunt (misschien de allernieuwste mysql inmiddels ook?), maar misschien heb je hier wat aan:
http://explainextended.co...rchical-queries-in-mysql/

edit: even geprobeerd (met de gratis Oracle XE DB):

SQL:

select SYS_CONNECT_BY_PATH(cat.name, '/') "Path"
from   categories cat
where  CONNECT_BY_ISLEAF = 1 --selecteer alleen eindnodes
and    exists (select 1
               from   items i
               join   user_items ui
                 on   ui.item_id = i.item_id
               where  ui.user_id = 'johan'
               and    i.cat_id = cat.cat_id
           )
connect by prior cat.cat_id = cat.parent_id 
start with cat.parent_id is null --vind null logischer dan 0 om root aan te geven

Resultaat:

Path
/rootcat_naam10/subcat_naam20/subsubcat_naam30
/rootcat_naam10/subcat_naam20/subsubcat_naam42
/rootcat_naam10/subcat_naam21/subsubcat_naam50
/rootcat_naam12/subcat_naam37/subsubcat_naam45
/rootcat_naam12/subcat_naam37/subsubcat_naam48

Erg simpele query dus, komt geen php aan te pas!

[ Voor 69% gewijzigd door CyBeRSPiN op 25-02-2010 21:06 ]

[Kleus]

Janoz schreef op donderdag 25 februari 2010 @ 12:32:
[...]

De hoofd categorieen kun je, door joins te gebruiken, gewoon in 1 query ophalen. De items zul je vervolgens moeten onthouden. Het handigste is om hiervoor een map (wat een php array eigenlijk is) te gebruiken waarbij je de ID als key gebruikt. Hierdoor kun je erg makkelijk een item terug vinden. Ook kun je een lijst bijhouden met alle cat id's die je nog mist. Zolang immers de parent id niet 0 is en de id ook nog niet voorkomt in de map met items mis je deze nog.

Ik ben ook al een keer naar iets dergelijks op zoek geweest (multi-level menu opzetten; onderliggende items ophalen in zo min mogelijk queries) en zoals Janoz het beschrijft weet ik het weer...
Volgens mij is jouw idee redelijk uit te werken met eenzelfde aanpak en daar heeft Crisp een zeer interessante tweakblog over geschreven -> Klik.

Wellicht interessant om te bekijken...

[cariolive23]

CyBeRSPiN schreef op donderdag 25 februari 2010 @ 19:01:
Met een Oracle DB is zoiets veel simpeler op te lossen omdat deze hierarchische queries ondersteunt (misschien de allernieuwste mysql inmiddels ook?)

Idem dito met PostgreSQL en SQL Server, hebben hier ook geen enkele moeite mee. MySQL kan dit niet en dit staat (bij mijn weten) ook niet op de release kalender.

http://wiki.phpfreakz.nl/Common_Table_Expression