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

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	

Acuh das sieht komplizierter aus als es ist. Ich gehe mal weiter von Innen nach Aussen zum erklären was da abgeht.

Das Grundkonzept ist einfach. ich kombiniere

;vars

-- [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
• @cntEin Counter, der wird für die richtige Reihenfolge verwednet. 1….n. 1 = tiefster Level, n oberster Level

grid

-- [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`]² Zeilen.
loop_ros interessiert nicht weiter. Bei n nehmen wir noch den Titel und den Parent-ID mit

prepared_data

-- [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	

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

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 := (@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

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.

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

(
	SELECT @npid := n2.parentid
	FROM nav n2  
	WHERE n2.id = @pid
)	AS npid,

Hier wird der Parent des aktuelen Parents ermittelt.

@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

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	

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