Baumzusammenstellung mit Hilfe von SQL (MySQL)

[omata]

Ausgehend von diesem Thread, hier mal die Version für MySQL...

Folgende Tabelle habe ich für den Baum angelegt...

SQL-Code:

CREATE TABLE `nodes` (
  `id` int(10) unsigned NOT NULL,
  `parentid` int(10) unsigned default NULL,
  `bez` varchar(45) NOT NULL default '',
  `reihenfolge` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `FK_nodes_parentid` (`parentid`),
  CONSTRAINT `FK_nodes_parentid` FOREIGN KEY (`parentid`) REFERENCES `nodes` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Beispielinhalt...

SQL-Code:

+----+----------+-----+-------------+
| id | parentid | bez | reihenfolge |
+----+----------+-----+-------------+
| 1  | NULL     | A   | 1           |
| 2  | 1        | AA  | 1           |
| 3  | 1        | AB  | 2           |
| 4  | NULL     | B   | 2           |
| 5  | 4        | BA  | 1           |
| 6  | 4        | BB  | 2           |
| 7  | 1        | AC  | 3           |
| 8  | 7        | ACA | 1           |
+----+----------+-----+-------------+

Dann habe ich folgende Prozedur angelegt...

SQL-Code:

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

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

  IF NodeID IS NULL THEN
    INSERT tmpTable (id, Depth, NodePath)
    SELECT id, _Depth, id
    FROM nodes
    WHERE parentid IS NULL;
  ELSE
    INSERT tmpTable (id, Depth, NodePath)
    SELECT id, _Depth, id
    FROM nodes
    WHERE parentid = NodeID;
  END IF;
  IF ROW_COUNT() = 0 THEN
    SET _Done = 1;
  END IF;
  WHILE _Done = 0 DO
    SET _Depth = _Depth + 1;
    INSERT tmpTable2 (id, Depth, NodePath)
    SELECT t.id, _Depth, CONCAT(x.NodePath, '|', id)
    FROM nodes t
    INNER JOIN tmpTable x
      ON t.parentid = x.id
    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.bez, x.Depth, x.NodePath
  FROM nodes t
  INNER JOIN tmpTable x
    ON t.id = x.id
  ORDER BY x.NodePath;

  DROP TEMPORARY TABLE tmpTable;
  DROP TEMPORARY TABLE tmpTable2;
END

Ausgabe des gesamten Baums...

SQL-Code:

CALL proc_GetNodes(NULL);

+------+-------------+
| ID   | Bez         |
+------+-------------+
| 1    | A           |
| 2    | AA          |
| 3    | AB          |
| 7    | AC          |
| 8    | ACA         |
| 4    | B           |
| 5    | BA          |
| 6    | BB          |
+------+-------------+

Ausgabe aller Elemente, die zum Element mit der ID = 1 gehören...

SQL-Code:

CALL proc_GetNodes(1);

+------+-------------+
| ID   | Bez         |
+------+-------------+
| 2    | AA          |
| 3    | AB          |
| 7    | AC          |
| 8    | ACA         |
+------+-------------+

Gruss
Thorsten

[omata]

Hier mal ein Beispiel für PHP...

Austauschbare Datenbankschicht (hier für MySQL)...

Code:

class ClassDBMySQL
{
  private $db;
  private $dbHOST;
  private $dbDATA;
  private $dbUSER;
  private $dbPASS;
  private $QUERY;

  public function __construct($dbHOST, $dbDATA, $dbUSER, $dbPASS) {
    $this->db = NULL;
    $this->dbHOST = $dbHOST;
    $this->dbDATA = $dbDATA;
    $this->dbUSER = $dbUSER;
    $this->dbPASS = $dbPASS;
    $this->QUERY = NULL;
  }

  public function Connect($database = '') {
    $this->db = mysql_connect($this->dbHOST, $this->dbUSER, $this->dbPASS);
    if ($this->db) {
      return $this->Select($database);
    } else {
      return false;
    }
  }

  public function Select($database = '') {
    if ($database == '') {
      $database = $this->dbDATA;
    }
    return mysql_select_db($database, $this->db);
  }

  public function Disconnect() {
    if ($this->db) {
      mysql_close($this->db);
    }
  }

  public function Error() {
    return mysql_error();
  }

  protected function OpenSQL($SQL) {
    if ($this->db) {
      return mysql_query($SQL, $this->db);
    }
  }

  protected function FreeSQL($Query) {
    if ($Query) {
      mysql_free_result($Query);
    }
  }

  protected function FetchSQL($Query) {
    if ($Query) {
      return mysql_fetch_array($Query);
    }
  }

}

Klasse zum Einlesen der Baumstruktur in ein Array...

Code:

class ClassDBTree
  extends ClassDBMySQL
{
  private $DATA;
  private $EOF;

  public function __construct($dbHOST, $dbDATA, $dbUSER, $dbPASS) {
    parent::__construct($dbHOST, $dbDATA, $dbUSER, $dbPASS);
    $this->DATA = NULL;
    $this->EOF = false;
  }

  private function SetNext() {
    if ($this->DATA = $this->FetchSQL($this->Query)) {
      $this->EOF = false;
    } else {
      $this->EOF = true;
    }
  }

  private function GetData() {
    return $this->DATA;
  }

  private function fillArray(&$Node, $Level) {
    $abbruch = false;
    while (!$this->EOF && !$abbruch) {
      $data = $this->GetData();
      $id = $data['id'];
      $Node[$id]['data'] = $data;
      $this->SetNext();
      $data = $this->GetData();
      if ($data['Depth'] > $Level) {
        $this->fillArray($Node[$id]['childs'], $Level+1);
      }
      if ($data['Depth'] < $Level) {
        $abbruch = true;
      }
    }
  }

  public function GetArray($SQL) {
    $result = array();
    $this->DATA = NULL;
    $this->EOF = false;
    $this->Query = $this->OpenSQL($SQL);
    $this->SetNext();
    $data = $this->GetData();
    $this->fillArray($result, $data['Depth']);
    $this->FreeSQL($this->Query);
    return $result;
  }

}

Aufruf...

Code:

$Tree = new ClassDBTree('localhost', 'database', 'username', 'password');
if ($Tree->Connect()) {
  $Nodes = $Tree->GetArray("CALL proc_GetNodes(NULL)");
  $Tree->Disconnect();
  echo "<pre>";
  print_r($Nodes);
  echo "</pre>";
}

Ergebnis...

Code:

Array
(
    [1] => Array
        (
            [data] => Array
                (
                    [0] => 1
                    [ID] => 1
                    [1] => A
                    [bezeichnung] => A
                    [2] => 1
                    [Depth] => 1
                    [3] => 1
                    [NodePath] => 1
                )

            [childs] => Array
                (
                    [2] => Array
                        (
                            [data] => Array
                                (
                                    [0] => 2
                                    [ID] => 2
                                    [1] => AA
                                    [bezeichnung] => AA
                                    [2] => 2
                                    [Depth] => 2
                                    [3] => 1|2
                                    [NodePath] => 1|2
                                )

                        )

                    [3] => Array
                        (
                            [data] => Array
                                (
                                    [0] => 3
                                    [ID] => 3
                                    [1] => AB
                                    [bezeichnung] => AB
                                    [2] => 2
                                    [Depth] => 2
                                    [3] => 1|3
                                    [NodePath] => 1|3
                                )

                            [childs] => Array
                                (
                                    [9] => Array
                                        (
                                            [data] => Array
                                                (
                                                    [0] => 9
                                                    [ID] => 9
                                                    [1] => ABC
                                                    [bezeichnung] => ABC
                                                    [2] => 3
                                                    [Depth] => 3
                                                    [3] => 1|3|9
                                                    [NodePath] => 1|3|9
                                                )

                                        )

                                )

                        )

                    [7] => Array
                        (
                            [data] => Array
                                (
                                    [0] => 7
                                    [ID] => 7
                                    [1] => AC
                                    [bezeichnung] => AC
                                    [2] => 2
                                    [Depth] => 2
                                    [3] => 1|7
                                    [NodePath] => 1|7
                                )

                            [childs] => Array
                                (
                                    [8] => Array
                                        (
                                            [data] => Array
                                                (
                                                    [0] => 8
                                                    [ID] => 8
                                                    [1] => ACA
                                                    [bezeichnung] => ACA
                                                    [2] => 3
                                                    [Depth] => 3
                                                    [3] => 1|7|8
                                                    [NodePath] => 1|7|8
                                                )

                                        )

                                )

                        )

                    [4] => Array
                        (
                            [data] => Array
                                (
                                    [0] => 4
                                    [ID] => 4
                                    [1] => B
                                    [bezeichnung] => B
                                    [2] => 1
                                    [Depth] => 1
                                    [3] => 4
                                    [NodePath] => 4
                                )

                        )

                    [5] => Array
                        (
                            [data] => Array
                                (
                                    [0] => 5
                                    [ID] => 5
                                    [1] => BA
                                    [bezeichnung] => BA
                                    [2] => 2
                                    [Depth] => 2
                                    [3] => 4|5
                                    [NodePath] => 4|5
                                )

                        )

                    [6] => Array
                        (
                            [data] => Array
                                (
                                    [0] => 6
                                    [ID] => 6
                                    [1] => BB
                                    [bezeichnung] => BB
                                    [2] => 2
                                    [Depth] => 2
                                    [3] => 4|6
                                    [NodePath] => 4|6
                                )

                        )

                )

        )

)

Gruss
Thorsten