[MySQL 3.x] meerdere geneste subqueries *

[DarkBlaze]

Ik ben bezig met het schrijven van een voorraadbeheer module maar nu zit ik met het volgende probleem.
De server waar hij op moet draaien gebruikt nog MySQL 3.x en ik gebruik tot 5 geneste subqueries in mijn SQL statements. Ik gebruik deze om te bepalen tot welke groep de getoonde gegevens behoren en
vervolgens dit ook van het bovenliggende niveau te weergeven. Er is maximaal een 6e niveau maar er kunnen ook minder niveau's zijn.

Het probleem is, dat de hoeveelheid gegevens die in een niveau staan nooit vast staat en dus dynamisch is. Er moet een overzicht gemaakt worden van alle gegevens die in de op dat moment geselecteerd niveau staan binnen een niveau groep of dmv een zoekopdracht zoals in de onderstaande query.

SQL:

SELECT data_sub5.cat_parrent AS sub_cat, data_sub5.data_data AS sub_data, data_sub5.data_ID AS sub_ID, data_sub5.data_parrent AS sub_parrent, data_sub4.data_data, data_sub4.cat_parrent AS data_parrent
FROM data_sub5
INNER JOIN data_sub4 ON data_sub5.data_parrent=data_sub4.data_ID
WHERE data_sub5.data_data LIKE '%$zoeken%' AND data_sub5.data_parrent IN (
  SELECT data_ID FROM data_sub4 WHERE data_ID IN (
    SELECT data_ID FROM data_sub3 WHERE data_ID IN (
      SELECT data_ID FROM data_sub2 WHERE data_ID IN (
        SELECT data_ID FROM data_sub1 where data_parrent IN (
          SELECT data_ID FROM data_group WHERE data_parrent = (
            SELECT group_name FROM cat_group WHERE group_ID = $id

[ Voor 2% gewijzigd door RobIII op 31-10-2007 00:46 . Reden: code tags toegevoegd en de query van 1 lange 'zin' omgezet naar iets leesbaars ]

[Verwijderd]

Ehm, als je MySQL 3 draait zou ik toch eens met je opdrachtgever gaan praten.

[curry684]

• Ik ga die query niet lezen tot ie tussen [code=sql] tags staat, maar volgens mij snap ik de essentie al wel
• 5 keer jezelf binnen joinen is een onzinnige constructie. Zelfs als er nu 'maximaal een 6e niveau' is gaat iemand op een dag niveaus 7 en 8 bedenken en dan heb je een fatale bug in je code
• Tip: schop de systeembeheerder. MySQL 3.x is al jaren deprecated en unsupported, en daarmee ongeveer net zo slim als het runnen van een server op Windows 95 met directe verbinding aan het internet. De MySQL devvers zijn al bezig aan versie 6 fyi
• Tip: haal de table flat binnen op basis van je zoekquery en construeer vervolgens in PHP een hierarchische structuur waar je recursief overheen itereert. Tikje beter voor je performance en wel bestand tegen 20 niveau's.

[DarkBlaze]

Ze hebben zelf al aangegeven dat ze eind dit jaar over willen op de nieuwste versies maar daar heb ik op dit moment nog niets aan (Zelf ook al aangegeven dat het vrij verouderd is). Ik heb eigenlijk ook geen tijd om het te gaan herschrijven want ik zit op dit moment niet erg ruim in mijn tijd door onder andere school en ik moet het eigenlijk morgen af hebben.
Ook niveau 7 & 8 zijn op dit moment even niet van belang dit is ook zo afgesproken.
Het moest allemaal snel snel mag niet veel kosten klaten wachten etc.
Misschien is een andere database opbouw een betere oplossing maar daar zat ik ook vast.

Wat ik in gedachten had was 3 tabellen.
Een met de catagorieën(niveau's) dus groep_ID + namen van elk niveau.
Tweede tabel voor alle 1e niveau gegevens met een relatie naar de categorieën tabel.
Derde tabel waar de rest in komt met een relatie op de tweede tabel.
Maar dat is zo even snel uit mij hoofd hierdoor krijg je dus ook niet het uitgebreide subquery probleem en dan kan je aan de hand van waar je bent in het programma wel bepalen bij welke groep hij hoort. Dit ook zodat als je de groep verwijderd/hernoemd hij alles weg gooit dvm cascade update/delete.

[Creepy]

En als toevoeging op curry684: lees ook nog *** Over topictitels in PRG - lezen voor topic openen!!! *** eens en geef aub via een TR een nieuwe topictitel door. Je titel nu zegt helemaal niks over je probleem.

[Voutloos]

DarkBlaze schreef op woensdag 31 oktober 2007 @ 01:10:
Het moest allemaal snel snel mag niet veel kosten klaten wachten etc.
Misschien is een andere database opbouw een betere oplossing maar daar zat ik ook vast

Ongehinderd door enige kennis over alle lopende projecten durf ik eigenlijk al te garanderen dat wachten op (helpen met) de mysql upgrade beter is voor de kosten.

Als systeem beheer geen zin heeft om unstable mysql 5.1 of 6 dingen te draaien mag dat, maar zonder 4.1 of 5.0 werken slaat echt nergens op. Je mist gewoon veel features alsmede een aantal zaken dat flink beter/netter geworden is. Vervolgens schrijf je een applicatie welke ook weer geport moet worden ten tijde van de upgrade...

En de query die je laat zien moet overigens wel om te zetten zijn naar eentje die enkel joins gebruikt.

offtopic:
En 'parent' is niet met dubbel 'r'.

[DexterDee]

Ik heb goede ervaringen met het "Modified Preorder Tree Traversal" design pattern. Hiermee kun je hierarchische data opslaan in een database en een willekeurige structuur met 1 query terugvragen. Recursie in PHP werkt ook, maar kan bij grote boomstructuren heel veel queries opleveren, wat de boel vertraagt.

Hier een link naar de theorie:
http://www.sitepoint.com/...rarchical-data-database/2

Je kunt met 1 query zowel het pad naar boven, naar beneden als alle children queryen van een bepaalde categorie. Enige kleine performance penalty die je hierbij hebt is het wijzigen van de structuur. Hier heb je wel meerdere queries voor nodig.

Pluspuntje: Dit werkt ook prima met MySQL 3, alhoewel ik het roerend eens ben met bovenstaande 'bezorgdheden'

[DarkBlaze]

Bedankt voor de reacties en zoals ik al aangegeven heb, ze willen nog niet upgraden nu omdat er nog veel meer op draait dat heb ik zelf ook al wel aangegeven. "Modified Preorder Tree Traversal" gaat niet werken omdat de gegevens in de database van te voren niet vast staan en de relaties binnen de groepen ook weer gewijzigd kunnen/mogen worden. Ik bewaar de huidige code ook sowieso wel.

[curry684]

curry684 schreef op woensdag 31 oktober 2007 @ 00:35:
• Tip: haal de table flat binnen op basis van je zoekquery en construeer vervolgens in PHP een hierarchische structuur waar je recursief overheen itereert. Tikje beter voor je performance en wel bestand tegen 20 niveau's.

^^^^^

[Verwijderd]

Precies, dat is een moeite van niets eigenlijk. Data ophalen, loopje bakken met mischien wat recursie als je het stoer wilt doen en klaar ben je.

Constructie's als die in de TS staan mogen imho direct door naar : [Alg] Slechtste programmeervoorbeelden deel 3

[ACM]

Deze query is overigens wel te doen in MySQL 3, maar het is waarschijnlijk het handigst om hem te herschrijven naar een aantal queries waarbij je een temporary table vult met de unieke id's die je over wilt houden en waar je dan vervolgens tegen joint met het niveau dat je uiteindelijk wilt hebben.