SQL - Recursive Abfrage

[webald]

Eine Frage an die SQL-Cracks:

Gegeben ist folgende Tabelle:
ID - ChildID
2  - 4
4  - 3
3  - 5
6  - 8
7  - 6

Ich möchte alle kinder und Enkel einer ID erhalten. Die Abfrage dazu sieht so aus:

Code: SQL  [Auswählen]

SELECT id, @lvl:=@lvl+1 AS 'depth', @pv:=childid AS 'childid' FROM mytable
JOIN
(SELECT @lvl:= 0, @pv:=2)tmp
WHERE id=@pv

depth ergibt die Hierarchie-Tiefe und ist später für die Priorisierung der Daten nötig.
@pv gibt den Startpunkt an.

Das Ergebnis für @pv:=2 ist korrekt.
Das Ergebnis für @pv:=7 ist falsch. Hier wird nur die letzte Zeile und depth =1 zurückgegeben.

Der Fehler liegt wohl an der Reihenfolge der Daten in der Tabelle. Gebe ich dieDaten so ein, dass die letzten beiden Datensätze die Reihenfolge tauschen ist das Ergebnis nämlich korrekt.

Zunächst die Tabelle nach ID zu sortieren führt hier nicht zum Erfolg, da id und childid nicht unbedingt in der richtigen Reihenfolge erfaßt werden, die Childid also mal großer oder kleiner als die zugehörige ID sein können.

Hat jemand eine SQL-Lösung hierfür oder ist das nur über PHP zu lösen?

Linkback: https://www.modified-shop.org/forum/index.php?topic=32023.0

[Matt]

Hat jemand eine SQL-Lösung hierfür oder ist das nur über PHP zu lösen?

Hast du Einfluß auf die Tabellenstruktur? Dann solltest du vielleicht einen Blick auf nested sets werfen.

Schon älterer Artikel dazu: http://reeg.junetz.de/DSP/node11.html#SECTION04344000000000000000

[webald]

Hi Matt,

den Gedanken hatte ich auch schon, aber ich scheitere daran, dass es keine 1-1 Beziehungen in der Tabelle gibt, sondern theoretisch n-m Beziehungen.

Die Tabelle könnte auch so aussehen:
ID - ChildID
2  - 4
4  - 3
3  - 5
6  - 8
7  - 6
4  - 6
3  - 7

Damit habe ich für ein und das selbe Child unterschiedliche Hierarchietiefen und gleichzeitig verschieden Parents. Die Hierarchietiefe ist nicht vorhersehbar.

Typisches Beispiel für so etwas ist etwa die Rechtevergabe über Usergruppen. Da kann eine Usergruppe verschiedene ander Gruppen enthalten - oder auch nicht.

[Matt]

Damit habe ich für ein und das selbe Child unterschiedliche Hierarchietiefen und gleichzeitig verschieden Parents. Die Hierarchietiefe ist nicht vorhersehbar.

Ja, das hab ich gemerkte, als ich mir dein Beispiel nachgebaut habe Deshalb ja der Vorschlag mit nested sets, da die beliebig tief sein können und trotzdem einfach abgefragt werden können.

[webald]

Hi Matt,

die Abfrage-Geschwindigkeit und -Einfachheit ist unbestritten, aber es lassen sich nur 1:n-Beziehungen abbilden, also 1 Parent mit n Kindern (oder menschlich nur alleinerziehende). Das was ich versuche darzustellen ist eine m:n-Beziehung, also mit mehreren Elternteilen oder Patchwork-Familie. Ich habe das mal zur Verdeutlichung in einige Grafiken gebracht.

nestedset1 => Standardmodell, Ausgangslage
nestedset2_insert => grün: hinzufügen eines neuen Elements, rot: notwendige Änderungen
nestedset3_multiparent => grün: neue Beziehung, blau: notwendige Änderung, aber undefiniert

Ich denke also, dass das mit nested-sets nicht darstellbar ist. Oder übersehe ich einen Ansatz?

[Matt]

Das stimmt, das geht nicht. Da hatte ich einen Denkfehler.