Auf Baumstruktur vom Blatt aus per SP zugreifen

[omata]

Schade, das du dort meinem Link nicht gefolgt bist.

SQL-Code:

CREATE TABLE Tree (
  ID       INTEGER,
  PARENTID INTEGER,
  CONSTRAINT PK_Tree PRIMARY KEY (ID),
  CONSTRAINT FK_Tree FOREIGN KEY (PARENTID) REFERENCES Tree (ID)
);

CREATE TABLE TreeData (
  ID     INTEGER,
  TreeID INTEGER,
  Data   BLOB,
  CONSTRAINT PK_TreeData PRIMARY KEY (ID),
  CONSTRAINT FK_TreeData FOREIGN KEY (TreeID) REFERENCES Tree (ID)
);

Tree:

Code:

ID | PARENTID
1  | NULL
2  | 1
3  | 2
4  | 1
5  | 4
6  | 5

Ich würde für die Wurzelknoten den Parentwert NULL benutzen, dann ist auch eine saubere SQL-Definition (siehe oben) möglich.

TreeData:

Code:

ID | TreeID | Data
1  | 1      | A
2  | 3      | B
3  | 4      | C
4  | 4      | D
5  | 5      | E
6  | 6      | F

SP:

SQL-Code:

CREATE PROCEDURE `proc_GetParents`(NodeID INTEGER)
BEGIN
  DECLARE _Depth INT DEFAULT 1;
  DECLARE _Done INT DEFAULT 0;

  CREATE TEMPORARY TABLE tmpTable (
    id INT PRIMARY KEY, parentid INT, Depth INT, NodePath VARCHAR(1000)
  ) TYPE=HEAP;
  CREATE TEMPORARY TABLE tmpTable2 (
    id INT PRIMARY KEY, parentid INT, Depth INT, NodePath VARCHAR(1000)
  ) TYPE=HEAP;

  INSERT tmpTable (id, parentid, Depth, NodePath)
  SELECT id, parentid, _Depth, id
  FROM tree
  WHERE id = NodeID;
  IF ROW_COUNT() = 0 THEN
    SET _Done = 1;
  END IF;
  WHILE _Done = 0 DO
    SET _Depth = _Depth + 1;
    INSERT tmpTable2 (id, parentid, Depth, NodePath)
    SELECT t.id, t.parentid, _Depth, CONCAT(x.NodePath, '|', t.id)
    FROM tree t
    INNER JOIN tmpTable x
      ON t.id = x.parentid
    WHERE x.Depth = _Depth-1;
    IF ROW_COUNT() = 0 THEN
      SET _Done = 1;
    END IF;
    INSERT tmpTable
    SELECT *
    FROM tmpTable2;
    DELETE FROM tmpTable2;
  END WHILE;

  SELECT t.id, t.parentid, x.Depth, x.NodePath, td.data
  FROM tree t
  INNER JOIN tmpTable x
    ON t.id = x.id
  LEFT JOIN treedata td
    ON t.id = td.treeid
  ORDER BY x.NodePath DESC;

  DROP TEMPORARY TABLE tmpTable;
  DROP TEMPORARY TABLE tmpTable2;
END

Aufruf:
CALL proc_GetParents(6); Ergebnis:

Code:

ID PARENTID Depth  NodePath Data
1  NULL     4      6|5|4|1  A
4  1        3      6|5|4    C
4  1        3      6|5|4    D
5  4        2      6|5      E
6  5        1      6        F