~~DISCUSSION~~
======Adjacency Tree: Pfad pro id======
=====Aufgabenstellung=====
So. Nun widmen wir uns also einer weiteren interessanten Aufgabe. Das Ziel ist es, zu jedem Node den ganzen Pfad auf einer Zeile anzuzeigen. Zudem wollen wir ein Tree-String. Die Sortierung des Tree soll das sort-feld berücksichtigen
====SQL Statmenent====
SELECT
l.grpid AS id,
MAX(l.cnt) AS lvl,
CONCAT(REPEAT('+ ', MAX(l.cnt) -1), l.grptitle) AS tree_title,
GROUP_CONCAT(p.title ORDER BY cnt DESC SEPARATOR ' -> ') AS lst_title,
CAST(GROUP_CONCAT(l.pid ORDER BY cnt DESC SEPARATOR '>') AS CHAR) AS lst_id,
GROUP_CONCAT(p.srt ORDER BY cnt DESC SEPARATOR '.') AS lst_sort
FROM
(
SELECT
n.id AS grpid,
n.title AS grptitle,
-- FLag, ob eine neue ID beginnt
@flag := (@last_id <> n.id) AS flag,
-- Counter pro ID setzen. Wird für die Sortierreihenfolge im GROUP_CONCAT() verwendet
CASE
WHEN @flag
THEN @cnt := 1
ELSE @cnt := @cnt+1
END AS cnt,
-- Parent ID ermitteln
CAST(CASE
WHEN @flag
THEN @pid := n.id
ELSE @pid := @npid
END AS SIGNED) AS pid,
-- Nächste Parent ID Ermitteln
(
SELECT @npid := n2.parentid
FROM nav n2
WHERE n2.id = @pid
) AS npid,
@last_id := n.id AS nid
FROM
(SELECT @last_id := 0, @det_grp := 0, @pid := 0, @npid := NULL, @cnt:=0) AS vars,
(SELECT n3.id FROM nav n3) AS loop_rows,
(SELECT n4.id, n4.title, n4.parentid FROM nav n4) AS n
) AS l
INNER JOIN (
-- Details zum Parent
SELECT id, title,
-- Sortieurng definieren
CAST(CASE
WHEN sort IS NULL
THEN CONCAT('C_', title)
ELSE CONCAT('B_', sort)
END AS CHAR) AS srt
FROM nav
) AS p
ON l.pid = p.id
GROUP BY
grpid,
grptitle
ORDER BY
lst_sort
====Ausgabe====
^ Asugabe von Beispiel 2: Pfad pro id ^^^^^^
^ id ^ lvl ^ tree_title ^ lst_title ^ lst_id ^ lst_sort ^
| 1 | 1 | Node #001 | Node #001 | 1 | C_Node #001 |
| 2 | 2 | + Node #002 | Node #001 -> Node #002 | 1>2 | C_Node #001.C_Node #002 |
| 8 | 3 | + + Node #008 | Node #001 -> Node #002 -> Node #008 | 1>2>8 | C_Node #001.C_Node #002.B_1 |
| 10 | 4 | + + + Node #010 | Node #001 -> Node #002 -> Node #008 -> Node #010 | 1>2>8>10 | C_Node #001.C_Node #002.B_1.C_Node #010 |
| 9 | 5 | + + + + Node #009 | Node #001 -> Node #002 -> Node #008 -> Node #010 -> Node #009 | 1>2>8>10>9 | C_Node #001.C_Node #002.B_1.C_Node #010.C_Node #009 |
| 3 | 3 | + + Node #003 | Node #001 -> Node #002 -> Node #003 | 1>2>3 | C_Node #001.C_Node #002.B_2 |
| 4 | 2 | + Node #004 | Node #001 -> Node #004 | 1>4 | C_Node #001.C_Node #004 |
| 5 | 3 | + + Node #005 | Node #001 -> Node #004 -> Node #005 | 1>4>5 | C_Node #001.C_Node #004.C_Node #005 |
| 6 | 4 | + + + Node #006 | Node #001 -> Node #004 -> Node #005 -> Node #006 | 1>4>5>6 | C_Node #001.C_Node #004.C_Node #005.C_Node #006 |
| 7 | 5 | + + + + Node #007 | Node #001 -> Node #004 -> Node #005 -> Node #006 -> Node #007 | 1>4>5>6>7 | C_Node #001.C_Node #004.C_Node #005.C_Node #006.C_Node #007 |
| 11 | 1 | Node #011 | Node #011 | 11 | C_Node #011 |
| 13 | 2 | + Node #013 | Node #011 -> Node #013 | 11>13 | C_Node #011.B_1 |
| 12 | 2 | + Node #012 | Node #011 -> Node #012 | 11>12 | C_Node #011.B_2 |
=====Erklärbär=====
Acuh das sieht komplizierter aus als es ist. Ich gehe mal weiter von Innen nach Aussen zum erklären was da abgeht.
====Konzept====
Das Grundkonzept ist einfach. ich kombiniere
====Variablen setzen====
-- [VARS]
(SELECT @last_id := 0, @pid := 0, @npid := NULL, @cnt:=0) AS var
Auch hier kommen wir nicht ohne Variablen aus - Sorry. In diesem Schritt initialisiere ich mal alle Variablen.
***@lastid** Mein gedächnis welche ID auf der Zeile davor war
***@pid** ParentID. ID des aktuellen Parent
***@npid** Next Parent ID. Also der Parent des aktuellen Parents
***@cnt**Ein Counter, der wird für die richtige Reihenfolge verwednet. 1....n. 1 = tiefster Level, n oberster Level
====Gird erstellen====
-- [GRID]
(SELECT n3.id FROM nav n3) AS loop_rows,
(SELECT n4.id, n4.title, n4.parentid FROM nav n4) AS n
Damit ich genügend Zeilen zur Verfügung habe, nehme ich einfach meine Tabelle und bringe sie ohne JOIN zusammen. Das gibt mit [Anzahl Zeilen von `nav`]2 Zeilen.
\\ loop_ros interessiert nicht weiter. Bei n nehmen wir noch den Titel und den Parent-ID mit
====Den Baum durchiterieren====
-- [PREPARED_DATA]
SELECT
n.id AS grpid,
n.title AS grptitle,
-- FLag, ob eine neue ID beginnt
@flag := (@last_id <> n.id) AS flag,
-- Counter pro ID setzen. Wird für die Sortierreihenfolge im GROUP_CONCAT() verwendet
CASE
WHEN @flag
THEN @cnt := 1
ELSE @cnt := @cnt+1
END AS cnt,
-- Parent ID ermitteln
CAST(CASE
WHEN @flag
THEN @pid := n.id
ELSE @pid := @npid
END AS SIGNED) AS pid,
-- Nächste Parent ID Ermitteln
(
SELECT @npid := n2.parentid
FROM nav n2
WHERE n2.id = @pid
) AS npid,
@last_id := n.id AS nid
FROM
(SELECT @last_id := 0, @pid := 0, @npid := NULL, @cnt:=0) AS vars,
(SELECT n3.id FROM nav n3) AS loop_rows,
(SELECT n4.id, n4.title, n4.parentid FROM nav n4) AS n
=== ID-Reihenfolge der Tabellenkombination ===
Jetzt gehe ich alle Zeilen durch. Die Reihenfolde der Daten ist nun wichtig. Wenn ich das obige SQL
mal kürze, dann sehen wir wie die ID-Reihenfolge ist. Wichtig, dass die 2te Spalte immer die gleichen iDs untereinander hat. Das macht MySQL selber schon richtig.
SELECT
myloop.id AS loop_id,
mylist.id AS list_id
FROM
(SELECT id FROM nav) AS myloop,
(SELECT id FROM nav) AS mylist
ORDER BY
mylist.id,
myloop.id
Das ergibt das folgende
^ loop_id ^ list_id ^
| 1 | 1 |
| 2 | 1 |
| ... | 1 |
| 13 | 1 |
| 1 | 2 |
| 2 | 2 |
| ... | 2 |
| 13 | 2 |
| ... | ... |
| 13 | 13 |
=== Die Formeln ===
Dann nehme ich mir mal die Formalen zur Brust.
Das Verfahren ist jetzt, dass wir mit der ersten n.id beginnen und von dieser den Parent bestimmen. Im nächsten Eintrag der n.id-Gruppe (siehe Spalte 2 im Kapitel vorher) bestimmen wir den Parent vom Parent. Dies geht so weiter, bis der Parent NULL ist.
n.id AS grpid,
n.title AS grptitle,
-- FLag, ob eine neue ID beginnt
@flag := (@last_id <> n.id) AS flag,
-- Counter pro ID setzen. Wird für die Sortierreihenfolge im GROUP_CONCAT() verwendet
CASE
WHEN @flag
THEN @cnt := 1
ELSE @cnt := @cnt+1
END AS cnt,
-- Parent ID ermitteln
CAST(CASE
WHEN @flag
THEN @pid := n.id
ELSE @pid := @npid
END AS SIGNED) AS pid,
-- Nächste Parent ID Ermitteln
(
SELECT @npid := n2.parentid
FROM nav n2
WHERE n2.id = @pid
) AS npid,
@last_id := n.id AS nid
== flag: Neue ID-Flag ==
@flag := (@last_id <> n.id) AS flag
\\ Wie wir weiter oben gesehen haben, ist die mylist nach der ID sortiert. Ich vergleiche nun, ob sich die ID
noch die gleiche wie auf der letzten Zeile ist und speichere das Resultat in die Variable @flag
== cnt: Counter ==
CASE
WHEN @flag
THEN @cnt := 1
ELSE @cnt := @cnt+1
END AS cnt,
Dies ist ein Counter, der bei jeder neuen ID wieder mit 1 beginnt.
== pid: Parent-ID==
CAST(CASE
WHEN @flag
THEN @pid := n.id
ELSE @pid := @npid
END AS SIGNED) AS pid,
Dies ist die ID des Parents des aktuellen Eintrages. Bei einer neuen ID-Gruppe wird die ID selber übernommen. Ansonsten ists der Parent, der in der Zeile davor als npid ermittelt wurde
== npid: Nächste Parent-ID ==
(
SELECT @npid := n2.parentid
FROM nav n2
WHERE n2.id = @pid
) AS npid,
Hier wird der Parent des aktuelen Parents ermittelt.
== nid: Nächste Last-ID ==
@last_id := n.id AS nid
Das Resultat sieht gekürzt dann etwa so aus. Die Spalte grpid ist die ID des nodes, pid die ID der Parents
^ grpid ^ grptitle ^ flag ^ cnt ^ pid ^ npid ^ nid ^
| 1 | Node #001 | 1 |1 | 1 | NULL | 1 |
| 1 | Node #001 | 0 |2 | NULL | NULL | 1 |
| ... |||||||
| 11 | Node #011 | 1 | 1 | 11 | NULL | 11 |
| 11 | Node #011 | 0 | 2 | NULL | NULL | 11 |
| ... |||||||
| 6 | Node #006 | 1 | 1 | 6 | 5 | 6 |
| 6 | Node #006 | 0 | 2 | 5 | 4 | 6 |
| 6 | Node #006 | 0 | 3 | 4 | 1 | 6 |
| 6 | Node #006 | 0 | 4 | 1 | NULL | 6 |
| 6 | Node #006 | 0 | 5 | NULL | NULL | 6 |
| ... |||||||
| 3 | Node #003 | 1 | 1 | 3 | 2 | 3 |
| 3 | Node #003 | 0 | 2 | 2 | 1 | 3 |
| 3 | Node #003 | 0 | 3 | 1 | NULL | 3 |
| 3 | Node #003 | 0 | 4 | NULL | NULL | 3 |
| ... |||||||
=====Infos Ergänzen, Gruppieren und Pfad bilden=====
So, jetzt nur noch alles zusammenpacken, die Infos der Nodes ergänzen und fertig.
Ich glaube, dieser Code-teil ist selbstsprechend
SELECT
l.grpid AS id,
MAX(l.cnt) AS lvl,
CONCAT(REPEAT('+ ', MAX(l.cnt) -1), l.grptitle) AS tree_title,
GROUP_CONCAT(p.title ORDER BY cnt DESC SEPARATOR ' -> ') AS lst_title,
CAST(GROUP_CONCAT(l.pid ORDER BY cnt DESC SEPARATOR '>') AS CHAR) AS lst_id,
GROUP_CONCAT(p.srt ORDER BY cnt DESC SEPARATOR '.') AS lst_sort
FROM
[PREPARED_DATA] AS l
INNER JOIN (
-- Details zum Parent
SELECT id, title,
-- Sortieurng definieren
CAST(CASE
WHEN sort IS NULL
THEN CONCAT('C_', title)
ELSE CONCAT('B_', sort)
END AS CHAR) AS srt
FROM nav
) AS p
ON l.pid = p.id
GROUP BY
grpid,
grptitle
ORDER BY
lst_sort
===== Sortierung =====
Nur noch soviel zur Sortierung.
Die Sortierung im Beispiel ist die folgende. Nodes mit einem sort-Eintrag haben vor den anderen Vorrang. Darim wird für die Sortierung ein B als Prefix verwendet. Bei den restlichen Feldern wird nach title sortiert. Mit dem Prefix C kommen sie definitiv nach den B-Nodes. Diese Sortierung kann beliebig im folgenden Code-Schnippsel angepasst werden.
CAST(CASE
WHEN sort IS NULL
THEN CONCAT('C_', title)
ELSE CONCAT('B_', sort)
END AS CHAR) AS srt
Der nachteil, Zaheln werden ebenfalls als Text sortiert. Ergo kommt 11 (''B_11'') vor 2 (''B_2'')
Als weiteres Beispiel eine Lösung, damit nach Zahlen und nach Text sortiert werden kann. Die Zahl sollte mehr als 10 Ziffern sein. Ansonsten muss der LPAD() angepasst werden
CASE
WHEN sort REGEXP '^[[:digit:]]+$'
THEN CONCAT('B_', LPAD(sort, 10, '0'))
WHEN NOT sort IS NULL
THEN CONCAT('B_', sort)
ELSE CONCAT('C_', title)
END AS srt_2
{{tag>MySQL Tree}}