[SQL] Hoe krijg ik bepaalde menuitems met 1 query

[RwD]

Ik heb voor een website een menustructuur en hiervan wil ik op iedere pagina weergeven:

• het huidige punt in de website
• alle DIRECTE kinderen van dit menu item
• alle (voor)ouders
• alle directe kinderen van de (voor)oudersalle sibblings van de (voor)ouders, dus alle items met dezelfde parent van iedere voorouder die opgehaald moet worden.

Dit zou dan visueel resulteren in een menu waarin alle subcategorieen tot aan het huidige punt open geklapt zijn met al hun directe kinderen zichtbaar. (Zoals wanneer je in windows naar c:\windows\temp zou gaan dan in de folder structuur alles zichtbaar wordt van c:\, c:\windows (en c:\temp als die submappen heeft))

Nu kan ik wel recursief vanaf het huidige punt steeds de menu_parent_id opvragen en daarmee zijn kinderen oppikken net zolang tot ik aan het hoogste punt ben gekomen, maar dat vindt ik geen mooie oplossing voor als de menustructuur redelijk diep is en ik vraag me af of het beter kan...

Nu heb ik hieronder even snel een menustructuur opgemaakt wat een mogelijkheid is op de site;

code:

+-------+---------+---------+----------+----------+--------------+----------+
|menu_id|menu_name|menu_left|menu_right|menu_level|menu_parent_id|menu_order| 
+-------+---------+---------+----------+----------+--------------+----------+
|     1 |Menuroot^|       1 |       26 |        0 |            0 |        1 |
|     2 |Start    |       2 |        3 |        1 |            1 |        1 |
|     4 |Over ons |       4 |        5 |        1 |            1 |        2 |
|     3 |Producten|       6 |       21 |        1 |            1 |        3 |
|     5 |Drukvorm |       7 |       12 |        2 |            3 |        1 |
|     7 |Boeken*  |       8 |        9 |        3 |            5 |        1 |
|     8 |Posters  |      10 |       11 |        3 |            5 |        2 |
|     6 |CD-ROM   |      13 |       18 |        2 |            3 |        2 |
|     9 |2004**   |      14 |       15 |        3 |            6 |        1 |
|    10 |2005**   |      16 |       17 |        3 |            6 |        2 |
|    11 |Online   |      19 |       20 |        2 |            3 |        3 |
|    12 |Vacatures|      22 |       23 |        1 |            1 |        4 |
|    13 |Contact  |      24 |       25 |        1 |            1 |        5 |
+-------+---------+---------+----------+----------+--------------+----------+
|^ nooit zichtbaar                                                          |
|* het "huidige" menu item                                                  |
|** het niet weer te geven item                                             |
+---------------------------------------------------------------------------+

Als ik in dit voorbeeld "Boeken" als huidige webpagina heb geopend, dan wil ik dat in dit voorbeeld alles zichtbaar wordt dat op een zelfde of lager niveau (level) staat dan "boeken" behalve de menu items waarvoor je eerst een niveau lager moet (-1) en dan weer omhoog (+1) om er te komen. Wat in dit voorbeeld 2004 en 2005 zijn.

Dus in plaats van dit:

code:

Menuroot^
 +- Start    
 |
 +- Over ons 
 |
 +- Producten
 |   +- Drukvorm 
 |   |   +- Boeken*
 |   |   |
 |   |   +- Posters
 |   |   
 |   +- CD-ROM
 |   |   +- 2004**  
 |   |   |
 |   |   +- 2005**  
 |   |
 |   +- Online   
 |
 +- Vacatures
 |
 +- Contact

Wil ik dit ophalen:

code:

Menuroot^
 +- Start    
 |
 +- Over ons 
 |
 +- Producten
 |   +- Drukvorm 
 |   |   +- Boeken*
 |   |   |
 |   |   +- Posters
 |   |   
 |   +- CD-ROM
 |   |
 |   +- Online   
 |
 +- Vacatures
 |
 +- Contact

En wel met 1 query

de menu_left, menu_right en menu_level zijn voor de nested set methode (of modified preorder tree traversal) en de menu_parent_id en menu_order velden zijn voor de voor iedereen bekende hierarchische aanpak.

Ik kan queries bedenken voor alle (voor)ouders + alle level 1 items

code:

SELECT   *
FROM     ew1_menu
WHERE    menu_level = 1
OR       (   menu_left <= 8
         AND menu_right >= 9 )
OR       (   menu_left > 8
         AND menu_right < 9
         AND menu_level = 4 )
ORDER BY menu_left
,        menu_order

Alleen laat die het item "posters" niet zien. Op deze wijze heb ik nog meer queries bedacht of proberen te bedenken, maar ik kom er steeds op uit dat ik recursie nodig heb. Of heb ik het mis??

[ Voor 8% gewijzigd door RwD op 03-10-2005 13:54 ]

[bigbeng]

edit:
vergeet eerdere meuk

Nieuwe poging, aanname is dat je het niveau tot je beschikking hebt van het huidige item.

code:

SELECT *
FROM menuitem
WHERE level <= <current_level>
OR parent_id = <current_id>

[ Voor 104% gewijzigd door bigbeng op 03-10-2005 12:14 ]

[Michali]

Mischien dat ik het probleem niet helemaal snap, maar volgens mij komt dit wel in de richting:

SQL:

SELECT
    a.*
FROM
    menuitem a,
    menuitem b
WHERE
    b.menu_name = 'Boeken'
AND
(
        a.menu_level < b.menu_level
    OR
        a.parent_id = b.parent_id
)

[RwD]

beide oplossingen die jullie geven zouden subitems van "Vacatures" terug geven als die deze zou hebben, dat wil ik dus niet. Jullie queries zijn alsof ik "c:\windows\temp" open en dat ineens de menu-structuur ook uitvouwt bij "c:\Program Files".

edit:
De tweede oplossing geeft alleen "Boeken" terug bedenk ik me. Dat is net iets te weinig

Ik wil alleen alle voorouders en eigenlijk niet hun directe kinderen, maar wel hun directe sibblings (items met dezelfde parent).

[ Voor 45% gewijzigd door RwD op 03-10-2005 12:37 ]

[curry684]

Dit kun je toch met modified preorder tree traversal oplossen?

[RwD]

curry684 schreef op maandag 03 oktober 2005 @ 12:42:
Dit kun je toch met modified preorder tree traversal oplossen?

Ja, en ik vraag dan ook hoe, want ik gebruik dat systeem al. Sterker nog, die link staat in mijn start post...

Om het duidelijker te maken hier een beter voorbeeld. Stel dat dit mijn complete menustructuur is:

code:

+- root
   |
   +- A
   |  |
   |  +- A1
   |  |  |
   |  |  +- A1a
   |  |  |
   |  |  +- A1b
   |  |
   |  +- A2
   |  |
   |  +- A3
   |     |
   |     +- A3a
   |
   +- B
   |  |
   |  +- B1
   |  |  |
   |  |  +- B1a
   |  |
   |  +- B2
   |  |  |
   |  |  +- B2a
   |  |  |
   |  |  +- B2b
   |  |  |  |
   |  |  |  +- B2b.1
   |  |  |
   |  |  +- B2c
   |  |
   |  +- B3
   |  |
   |  +- B4
   |
   +- C
      |
      +- C1
      |  |
      |  +- C1a
      |  |
      |  +- C1b
      |
      +- C2

En in mijn website ben ik op pagina "B2b", dan moet de weergegeven menustructuur de volgende zijn:

code:

+- root
   |
   +- A
   |
   +- B
   |  |
   |  +- B1
   |  |
   |  +- B2
   |  |  |
   |  |  +- B2a
   |  |  |
   |  |  +- B2b
   |  |  |  |
   |  |  |  +- B2b.1
   |  |  |
   |  |  +- B2c
   |  |
   |  +- B3
   |  |
   |  +- B4
   |
   +- C

Zoals je ziet geef ik hier

• Alle kinderen weer van "B2b" (namelijk B2b.1)
• Alle voorouders (namelijk "B2", "B" en "root")
• Alle sibblings van de voorouders. (namelijk:
  voorouder "B2" -> "B1", "B3" en "B4";
  voorouder "B" -> "A" en "C";
  voorouder "root" -> *geen sibblings*)
• En ik was vergeten te vermelden alle normale sibblings ook te willen zien (namelijk "B2a" en "B2c")

Ik hoop dat ik nu eindelijk duidelijk ben geweest want eerlijk gezegd zou ik mijn startpost ook niet zeker weten meteen snappen

[ Voor 7% gewijzigd door RwD op 03-10-2005 12:54 ]

[Michali]

RwD schreef op maandag 03 oktober 2005 @ 12:32:
beide oplossingen die jullie geven zouden subitems van "Vacatures" terug geven als die deze zou hebben, dat wil ik dus niet. Jullie queries zijn alsof ik "c:\windows\temp" open en dat ineens de menu-structuur ook uitvouwt bij "c:\Program Files".

Volgens het, inmiddels aangepaste, voorbeeld in je startpost werkt het wel. Dit wisten we dus niet.

edit:
De tweede oplossing geeft alleen "Boeken" terug bedenk ik me. Dat is net iets te weinig

Hoezo? Je ziet toch dat b op 'Boeken' moet matchen en dat ik a.* selecteer? En van a hoeft de naam niet gelijk aan 'Boeken' te zijn. Wat bij a wel een restrictie is, is dat menu_level kleiner moet zijn dan die van b of de parent_id moet overeen komen met die van b.

[ Voor 7% gewijzigd door Michali op 03-10-2005 13:08 ]

[curry684]

RwD schreef op maandag 03 oktober 2005 @ 12:52:
[...]
Ja, en ik vraag dan ook hoe, want ik gebruik dat systeem al. Sterker nog, die link staat in mijn start post...

Pardon, meende aan je tabellen een andere structuur te herkennen * curry684 moet niet intelligent proberen te doen op maandagochtend

[RwD]

Michali schreef op maandag 03 oktober 2005 @ 13:07:
[...]

Volgens het, inmiddels aangepaste, voorbeeld in je startpost werkt het wel. Dit wisten we dus niet.

Nee hoor, het werkt nog niet Met het voorbeeld uit mijn start post heb ik niet de sibblings van alle items die ik ophaal

[...]

Hoezo? Je ziet toch dat b op 'Boeken' moet matchen en dat ik a.* selecteer? En van a hoeft de naam niet gelijk aan 'Boeken' te zijn. Wat bij a wel een restrictie is, is dat menu_level kleiner moet zijn dan die van b of de parent_id moet overeen komen met die van b.

Ja, ik zag het bij een tweede inspectie wel; ik heb eigenlijk nog nooit eerder zo'n query gezien
Maar in ieder geval haalt jouw query dan nog subitems van "Vacatures" op omdat hun level ook lager is dan dat van boeken...

curry684 schreef op maandag 03 oktober 2005 @ 13:08:
[...]

Pardon, meende aan je tabellen een andere structuur te herkennen * curry684 moet niet intelligent proberen te doen op maandagochtend

Ik heb beide structuren, ze zijn wel handig in combinatie omdat ik dan (bijna??) altijd alles op kan halen met 1 query. Het onderhoud kost er misschien meer; maar ik haal meer gegevens op dan dat ik aan onderhoud doe dus dat is ok...

[ Voor 31% gewijzigd door RwD op 03-10-2005 13:31 ]

[Michali]

RwD schreef op maandag 03 oktober 2005 @ 13:27:
[...]
Nee hoor, het werkt nog niet Met het voorbeeld uit mijn start post heb ik niet de sibblings van alle items die ik ophaal

Ik weet dat het niet werkt. Maar volgens je eerste voorbeeld (wat inmiddels dus aangepast is, dat bedoelde ik ), werkte het volgens mij wel. Nu de eisen iets scherper zijn werkt het dus niet. Geen idee overigens hoe je dit wel voor elkaar kan krijgen.

[Confusion]

Volgens mij staan je menu_id's niet op volgorde en dan vind ik het altijd wel makkelijk als je die er even bijzet. Is dat mogelijk?

Edit:
Als je dus bij element foo zit, dan wil je ophalen:
• Alle elementen met menu_parent_id == foo.menu_id (de kinderen)
• Alle elementen met menu_parent_id == foo.menu_parent_id && menu_order != foo.menu_order (de gewone siblings)
• Alle elementen met menu_level <= foo.menu_level (de ouders, grootouders, etc., inclusief hun siblings en kinderen). Maar hieruit wil je de kinderen filteren. Kinderen van siblings van jouw directe voorlopers zijn de elementen die niet een directe voorloper van jou als menu_parent_id hebben. Je moet dus eerst je directe voorlopers achterhalen (dat zijn alle elementen waarvoor geldt menu_left < foo.menu_left en menu_right > foo.menu_right) en vervolgens moet je van ieder element van de subselectie gaan toetsen of ze een directe voorloper als ouder hebben. Volgens mij is dat een draak van een query, als het uberhaupt al in 1 query kan. Volgens mij kan je beter meer gegevens ophalen en het scriptmatig doen...

[ Voor 197% gewijzigd door Confusion op 03-10-2005 14:07 ]

[RwD]

Confusion schreef op maandag 03 oktober 2005 @ 13:39:
Volgens mij staan je menu_id's niet op volgorde en dan vind ik het altijd wel makkelijk als je die er even bijzet. Is dat mogelijk?

Ja, alhoewel ik ze er bij heb moeten verzinnen, want ik wilde niet de hele tabel opnieuw genereren hopelijk staan de id's goed.

[RwD]

*kick*

Even zien of er momenteel mensen zijn die wel een mogelijkheid zien om mijn vraagstuk met 1 query te beantwoorden. Ik heb nog steeds geen mogelijkheden gezien.

Zijn er dingen die alle nodes die ik wil hebben met elkaar delen?? Left, right, level en parent_id mag door elkaar gebruikt worden als nodig...

Subqueries kunnen ook tegenwoordig geloof ik en alhoewel ik ze liever niet gebruik is het misschien mogelijk alle ancestors op te vragen, en dan alle kinderen met een parent_id van 1 van de ancestors. Of beeld ik me dat nog in op de vroege ochtend (heb op moment van schrijven even geen mogelijkheden om te checken)

Die query zou iets dergelijks worden als:

code:

SELECT   *
FROM     ew1_menu
WHERE    menu_parent_id IN
         (SELECT   menu_id
          FROM     ew1_menu
          WHERE    menu_left <= 8
          AND      menu_right >= 9)
OR       (   menu_left <= 8
         AND menu_right >= 9)
ORDER BY menu_left

Volgens mij doet deze query precies wat ik wil, alleen is deze met subquery. Kan het ook zonder???

edit:
Ik vermoed dat de query werkt omdat de subquery alle (voor)ouders en het item zelf ophaalt. Van deze items worden alle kinderen geselecteerd. En om (als nodig) de root er ook bij te betrekken voeg ik die laatste OR toe.

[ Voor 14% gewijzigd door RwD op 05-10-2005 09:55 ]

[Confusion]

Zo op het eerste gezicht haalt die query inderdaad alle voorouders en de directe kinderen van die voorouders op. Als je die AND met de twee andere queries, dan zou je alles weleens te pakken kunnen hebben. Zonder subquery is volgens mij sowieso onmogelijk, omdat je moet selecteren op de menu_id's van de voorouders en die moet je dus eerst ophalen.

[RwD]

Confusion schreef op maandag 03 oktober 2005 @ 13:39:
Volgens mij staan je menu_id's niet op volgorde en dan vind ik het altijd wel makkelijk als je die er even bijzet. Is dat mogelijk?

Edit:
Als je dus bij element foo zit, dan wil je ophalen:
• Alle elementen met menu_parent_id == foo.menu_id (de kinderen)
• Alle elementen met menu_parent_id == foo.menu_parent_id && menu_order != foo.menu_order (de gewone siblings)
• Alle elementen met menu_level <= foo.menu_level (de ouders, grootouders, etc., inclusief hun siblings en kinderen). Maar hieruit wil je de kinderen filteren. Kinderen van siblings van jouw directe voorlopers zijn de elementen die niet een directe voorloper van jou als menu_parent_id hebben. Je moet dus eerst je directe voorlopers achterhalen (dat zijn alle elementen waarvoor geldt menu_left < foo.menu_left en menu_right > foo.menu_right) en vervolgens moet je van ieder element van de subselectie gaan toetsen of ze een directe voorloper als ouder hebben. Volgens mij is dat een draak van een query, als het uberhaupt al in 1 query kan. Volgens mij kan je beter meer gegevens ophalen en het scriptmatig doen...

Naja, draak van een query... Volgens mij zoek je te ingewikkeld, met een subquery is het sowieso niet erg veel werk (zie vorige post) Bovendien wil jij kinderen selecteren die je er daarna weer uit wilt filteren, dat is een beetje omslachtig en zal ik moeten vermijden.

Misschien is het zonder subquery wel een draak van een query trouwens, maar ik ben er nog niet uit. Ik hoop dat mijn subquery oplossing iemand anders op het goede spoor zet, ikzelf zit nog op een wissel te wachten

[RwD]

Confusion schreef op woensdag 05 oktober 2005 @ 09:56:
Zo op het eerste gezicht haalt die query inderdaad alle voorouders en de directe kinderen van die voorouders op. Als je die AND met de twee andere queries, dan zou je alles weleens te pakken kunnen hebben.

Ik weet niet precies wat je bedoelt met het AND gedeelte, want als ik het helemaal logisch bekijk werkt dit al. Ik ren zometeen even naar mn andere pc hier om de query uit te proberen.

Zonder subquery is volgens mij sowieso onmogelijk, omdat je moet selecteren op de menu_id's van de voorouders en die moet je dus eerst ophalen.

Nou, ik hoopte dus dat op een of andere manier de left en right van de items aan bepaalde eigenschappen voldoen die de andere items' left en right niet hebben... Want met parent-child methodes was ik al lang uitgekeken geweest omdat het daar zeker niet mogelijk is zonder recursie (die methode haalt het met een subquery volgens mij niet eens)

edit
Alternatief is dat ik als tijdelijke oplossing met een variabele de SQL versie ophaal om te weten of deze subqueries ondersteund. Ik weet alleen niet of ik kan opvragen of hij het ondersteund of dat ik met een lijstje moet gaan werken voor iedere SQL versie. Als ik weet dat subqueries niet ondersteund worden kan ik met query 1 alle ancestors + node zelf ophalen en met query 2 alle kinderen + root element selecteren.

Ik ben nog steeds op zoek naar een universele 1 query oplossing!

[ Voor 22% gewijzigd door RwD op 05-10-2005 10:12 ]