Till: verschachtelte/hierachische SQL-Abfrage

Hallo,

ich hätte mal eine Frage, wie ich eine verschachtelte/hierachische Abfrage mit MySQL realisieren kann. Hintergrund ist eine in der Datenbank gespeichert Menüstruktur - diese will ich, z.B. für eine Sitemap - auslesen. Und zwar in eben der Anordnung einer Sitemap. Die Tabelle ist so aufgebaut, dass jederzeit neue Menüpunkte (automatisch) und Hierachieebenen hinzugeführt werden können. Hier ein Beispiel anhand von Ländern ...

id      top_id  ebene   reihenf land

1       0       0       1       erde
2       4       2       2       china
3       20      2       1       deutschland
4       1       1       2       asien
6       4       2       1       buthan
7       1       1       1       afrika
9       20      2       2       frankreich
10      3       3       2       hessen
11      3       3       1       bayern
19      7       2       3       kenya
20      1       1       3       europa
22      7       2       2       botswana
...

id ist die fortlaufende ID, top_id die ID des Vater-Elementes, die Ebene (hier zusätzlich eingeführt) bezeichnet die Hierachie-Stufe, reihenf die Reihenfolge.

Und so (in dieser Reihenfolge) sollte die Liste ausgegeben werden:

-> erde
   -> afrika
      -> botswana
      -> kenya
   -> asien
      -> buthan
      -> china
   -> europa
      -> deutschland
         -> bayern
         -> hessen
      -> frankreich

Vielen Dank für Eure Tips! Wenn jemand eine bessere Idee für einen Tabellen-Aufbau hat ...

Grüße, Till

  1. Hi,

    Variante 1) löse das ganze in einer Schleife ausserhalb von MySQL/SQL mittels PHP

    Variante 2) Beschäftige dich mal mit dem Thema "Nested Sets", das ist ein Pattern für Hierarchieabbildung in relationalen Tabellen / Datenbanken, es hat seine Hauptvorteile auf der lesenden Seite, da zum Updaten immer mind 3. Updates in einer Transaktion notwendig sind

    Variante 3) Verwende keine relationale Datenbank, sondern XML

    Variante 4) Wenn du die Tiefe der Verschachtelung einschätzen kannst, z.b. max 4, dann joine die Tabelle oft genug mit sich selbst (LEFT JOINS), dann bekommst du eine horizontale ausgabe mit Spalten à la "1. Level, 2. Level, 3. Level, 4. Level" ...

    Variante 5) Strukturiere deine Tabelle gleich so:

    Universum     | Galaxie        |   Planet   | Kontinent    | Land      | Region    | Stadt
    ------------------------------------------------------------------------------------------
    Underverse    | Milchstrasse   |   Erde     | Afrika       | Simbabwe  | Südwesten | Harare

    Die Struktur ist trivial ;)

    Reicht dir das etwa an Varianten?

    Ciao, Frank

    1. Hi Frank!

      Vielen Dank für Deine ausführliche Antwort.

      Variante 1) löse das ganze in einer Schleife ausserhalb von MySQL/SQL mittels PHP

      Ja, nur das wird auch nicht so einfach ... ;)

      Variante 2) Beschäftige dich mal mit dem Thema "Nested Sets", das ist ein Pattern für Hierarchieabbildung in relationalen Tabellen / Datenbanken, es hat seine Hauptvorteile auf der lesenden Seite, da zum Updaten immer mind 3. Updates in einer Transaktion notwendig sind

      Werde ich mir mal in Ruhe anschauen ... Danke für das Stichwort.

      Variante 3) Verwende keine relationale Datenbank, sondern XML

      Scheidet wohl aus.

      Variante 4) Wenn du die Tiefe der Verschachtelung einschätzen kannst, z.b. max 4, dann joine die Tabelle oft genug mit sich selbst (LEFT JOINS), dann bekommst du eine horizontale ausgabe mit Spalten à la "1. Level, 2. Level, 3. Level, 4. Level" ...

      Variante 5) Strukturiere deine Tabelle gleich so:

      Universum     | Galaxie        |   Planet   | Kontinent    | Land      | Region    | Stadt

      Underverse    | Milchstrasse   |   Erde     | Afrika       | Simbabwe  | Südwesten | Harare

      Ja, genau darauf war ich jetzt auch schon gekommen. Um es einfach zu halten, werde ich es wahrscheinlich so lösen ...

      Reicht dir das etwa an Varianten?

      Danke, danke, das dürfte reichen. ;)

      ps: Harare liegt nicht gerade im Südwesten von Zimbabwe ;)

    2. ps: Habe das ganze jetzt doch mit nested sets gelöst - das macht Spass! ;) Erinnere mich auch wieder an eine Vorlesung, wo genau dieser Ansatz behandelt wurde im Zusammenhang mit Suchoptimierung ...

      Grüße, Till