Ich habe gerade den Aktualisierungs-/Hinzufügungs-/Löschteil für die Methode „Abschlusstabelle“ zum Organisieren hierarchischer Abfragedaten erstellt, die auf Seite 70 in diesem Slideshare gezeigt werden: http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back
Meine Datenbank sieht so aus:
Tabellenkategorien:
ID Name
1 Top value
2 Sub value1
Tabellen-Kategoriebaum:
child parent level
1 1 0
2 2 0
2 1 1
Ich habe jedoch ein kleines Problem, den vollständigen Baum als mehrdimensionales Array aus einer einzigen Abfrage zurückzubekommen.
Hier ist, was ich zurückbekommen möchte:
array (
'topvalue' = array (
'Subvalue',
'Subvalue2',
'Subvalue3)
);
);
Aktualisieren:
Ich habe diesen Link gefunden, aber es fällt mir immer noch schwer, ihn in ein Array umzuwandeln:
http://karwin.blogspot.com/2010/03/rendering-trees-with-closure-tables.html
Update2:
Ich konnte jetzt jeder der Kategorien Tiefen hinzufügen, falls das hilfreich sein kann.
Okay, ich habe PHP-Klassen geschrieben, die die Tabellen-, Zeilen- und Rowset-Klassen der Zend Framework DB erweitern. Ich habe das sowieso entwickelt, weil ich hier spreche PHP Tek-X in ein paar Wochen über hierarchische Datenmodelle.
Ich möchte nicht meinen gesamten Code in Stack Overflow posten, da sie implizit unter Creative Commons lizenziert werden, wenn ich das tue. aktualisieren: Ich habe meinen Code dem übergeben Inkubator für Zend Framework-Extras und meine Präsentation ist Modelle für hierarchische Daten mit SQL und PHP bei Slideshare.
Ich werde die Lösung in Pseudocode beschreiben. Ich verwende zoologische Taxonomie als Testdaten, heruntergeladen von ITIS.gov. Der Tisch ist longnames:
CREATE TABLE `longnames` (
`tsn` int(11) NOT NULL,
`completename` varchar(164) NOT NULL,
PRIMARY KEY (`tsn`),
KEY `tsn` (`tsn`,`completename`)
)
Ich habe eine erstellt Verschlusstabelle für die Pfade in der Hierarchie der Taxonomie:
CREATE TABLE `closure` (
`a` int(11) NOT NULL DEFAULT '0', -- ancestor
`d` int(11) NOT NULL DEFAULT '0', -- descendant
`l` tinyint(3) unsigned NOT NULL, -- levels between a and d
PRIMARY KEY (`a`,`d`),
CONSTRAINT `closure_ibfk_1` FOREIGN KEY (`a`) REFERENCES `longnames` (`tsn`),
CONSTRAINT `closure_ibfk_2` FOREIGN KEY (`d`) REFERENCES `longnames` (`tsn`)
)
Angesichts des Primärschlüssels eines Knotens können Sie alle seine Nachkommen auf diese Weise erhalten:
SELECT d.*, p.a AS `_parent`
FROM longnames AS a
JOIN closure AS c ON (c.a = a.tsn)
JOIN longnames AS d ON (c.d = d.tsn)
LEFT OUTER JOIN closure AS p ON (p.d = d.tsn AND p.l = 1)
WHERE a.tsn = ? AND c.l <= ?
ORDER BY c.l;
Die Verbindung zu closure AS p soll die übergeordnete ID jedes Knotens enthalten.
Die Abfrage nutzt Indizes ziemlich gut:
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+
| 1 | SIMPLE | a | const | PRIMARY,tsn | PRIMARY | 4 | const | 1 | Using index; Using filesort |
| 1 | SIMPLE | c | ref | PRIMARY,d | PRIMARY | 4 | const | 5346 | Using where |
| 1 | SIMPLE | d | eq_ref | PRIMARY,tsn | PRIMARY | 4 | itis.c.d | 1 | |
| 1 | SIMPLE | p | ref | d | d | 4 | itis.c.d | 3 | |
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+
Und da ich 490.032 Zeilen habe longnames und 4.299.883 Zeilen hinein closurees läuft in ziemlich guter Zeit:
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000257 |
| Opening tables | 0.000028 |
| System lock | 0.000009 |
| Table lock | 0.000013 |
| init | 0.000048 |
| optimizing | 0.000032 |
| statistics | 0.000142 |
| preparing | 0.000048 |
| executing | 0.000008 |
| Sorting result | 0.034102 |
| Sending data | 0.001300 |
| end | 0.000018 |
| query end | 0.000005 |
| freeing items | 0.012191 |
| logging slow query | 0.000008 |
| cleaning up | 0.000007 |
+--------------------+----------+
Jetzt bearbeite ich das Ergebnis der obigen SQL-Abfrage nach und sortiere die Zeilen in Teilmengen gemäß der Hierarchie (Pseudocode):
while ($rowData = fetch()) {
$row = new RowObject($rowData);
$nodes[$row["tsn"]] = $row;
if (array_key_exists($row["_parent"], $nodes)) {
$nodes[$row["_parent"]]->addChildRow($row);
} else {
$top = $row;
}
}
return $top;
Ich definiere auch Klassen für Rows und Rowsets. Ein Rowset ist im Grunde ein Array von Zeilen. Eine Zeile enthält ein assoziatives Array von Zeilendaten sowie ein Rowset für seine untergeordneten Elemente. Das untergeordnete Rowset für einen Blattknoten ist leer.
Zeilen und Rowsets definieren auch aufgerufene Methoden toArrayDeep() die ihren Dateninhalt rekursiv als einfaches Array ausgeben.
Dann kann ich das ganze System wie folgt zusammen verwenden:
// Get an instance of the taxonomy table data gateway
$tax = new Taxonomy();
// query tree starting at Rodentia (id 180130), to a depth of 2
$tree = $tax->fetchTree(180130, 2);
// dump out the array
var_export($tree->toArrayDeep());
Die Ausgabe ist wie folgt:
array (
'tsn' => '180130',
'completename' => 'Rodentia',
'_parent' => '179925',
'_children' =>
array (
0 =>
array (
'tsn' => '584569',
'completename' => 'Hystricognatha',
'_parent' => '180130',
'_children' =>
array (
0 =>
array (
'tsn' => '552299',
'completename' => 'Hystricognathi',
'_parent' => '584569',
),
),
),
1 =>
array (
'tsn' => '180134',
'completename' => 'Sciuromorpha',
'_parent' => '180130',
'_children' =>
array (
0 =>
array (
'tsn' => '180210',
'completename' => 'Castoridae',
'_parent' => '180134',
),
1 =>
array (
'tsn' => '180135',
'completename' => 'Sciuridae',
'_parent' => '180134',
),
2 =>
array (
'tsn' => '180131',
'completename' => 'Aplodontiidae',
'_parent' => '180134',
),
),
),
2 =>
array (
'tsn' => '573166',
'completename' => 'Anomaluromorpha',
'_parent' => '180130',
'_children' =>
array (
0 =>
array (
'tsn' => '573168',
'completename' => 'Anomaluridae',
'_parent' => '573166',
),
1 =>
array (
'tsn' => '573169',
'completename' => 'Pedetidae',
'_parent' => '573166',
),
),
),
3 =>
array (
'tsn' => '180273',
'completename' => 'Myomorpha',
'_parent' => '180130',
'_children' =>
array (
0 =>
array (
'tsn' => '180399',
'completename' => 'Dipodidae',
'_parent' => '180273',
),
1 =>
array (
'tsn' => '180360',
'completename' => 'Muridae',
'_parent' => '180273',
),
2 =>
array (
'tsn' => '180231',
'completename' => 'Heteromyidae',
'_parent' => '180273',
),
3 =>
array (
'tsn' => '180213',
'completename' => 'Geomyidae',
'_parent' => '180273',
),
4 =>
array (
'tsn' => '584940',
'completename' => 'Myoxidae',
'_parent' => '180273',
),
),
),
4 =>
array (
'tsn' => '573167',
'completename' => 'Sciuravida',
'_parent' => '180130',
'_children' =>
array (
0 =>
array (
'tsn' => '573170',
'completename' => 'Ctenodactylidae',
'_parent' => '573167',
),
),
),
),
)
Beziehen Sie sich auf Ihren Kommentar zur Berechnung der Tiefe – oder wirklich der Länge jedes Pfades.
Angenommen, Sie haben gerade einen neuen Knoten in Ihre Tabelle eingefügt, der die eigentlichen Knoten enthält (longnames im obigen Beispiel) wird die ID des neuen Knotens von zurückgegeben LAST_INSERT_ID() in MySQL oder sonst kannst du es irgendwie bekommen.
INSERT INTO Closure (a, d, l)
SELECT a, LAST_INSERT_ID(), l+1 FROM Closure
WHERE d = 5 -- the intended parent of your new node
UNION ALL SELECT LAST_INSERT_ID(), LAST_INSERT_ID(), 0;
Vorgeschlagene Lösung
Dieses folgende Beispiel gibt etwas mehr als Sie verlangen, aber es ist eine wirklich nette Art, dies zu tun, und zeigt dennoch, woher die Informationen in jeder Phase stammen.
Es verwendet die folgende Tabellenstruktur:
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| parent | int(10) unsigned | NO | | NULL | |
| name | varchar(45) | NO | | NULL | |
+--------+------------------+------+-----+---------+----------------+
Hier ist es:
<?php
// Connect to the database
mysql_connect('localhost', 'root', '');
mysql_select_db('test');
echo '<pre>';
$categories = Category::getTopCategories();
print_r($categories);
echo '</pre>';
class Category
{
/**
* The information stored in the database for each category
*/
public $id;
public $parent;
public $name;
// The child categories
public $children;
public function __construct()
{
// Get the child categories when we get this category
$this->getChildCategories();
}
/**
* Get the child categories
* @return array
*/
public function getChildCategories()
{
if ($this->children) {
return $this->children;
}
return $this->children = self::getCategories("parent = {$this->id}");
}
////////////////////////////////////////////////////////////////////////////
/**
* The top-level categories (i.e. no parent)
* @return array
*/
public static function getTopCategories()
{
return self::getCategories('parent = 0');
}
/**
* Get categories from the database.
* @param string $where Conditions for the returned rows to meet
* @return array
*/
public static function getCategories($where="")
{
if ($where) $where = " WHERE $where";
$result = mysql_query("SELECT * FROM categories$where");
$categories = array();
while ($category = mysql_fetch_object($result, 'Category'))
$categories[] = $category;
mysql_free_result($result);
return $categories;
}
}
Testfall
In meiner Datenbank habe ich folgende Zeilen:
+----+--------+-----------------+
| id | parent | name |
+----+--------+-----------------+
| 1 | 0 | First Top |
| 2 | 0 | Second Top |
| 3 | 0 | Third Top |
| 4 | 1 | First Child |
| 5 | 1 | Second Child |
| 6 | 2 | Third Child |
| 7 | 2 | Fourth Child |
| 8 | 4 | First Subchild |
| 9 | 4 | Second Subchild |
+----+--------+-----------------+
Und damit gibt das Skript folgende (längere) Informationen aus:
Array
(
[0] => Category Object
(
[id] => 1
[parent] => 0
[name] => First Top
[children] => Array
(
[0] => Category Object
(
[id] => 4
[parent] => 1
[name] => First Child
[children] => Array
(
[0] => Category Object
(
[id] => 8
[parent] => 4
[name] => First Subchild
[children] => Array
(
)
)
[1] => Category Object
(
[id] => 9
[parent] => 4
[name] => Second Subchild
[children] => Array
(
)
)
)
)
[1] => Category Object
(
[id] => 5
[parent] => 1
[name] => Second Child
[children] => Array
(
)
)
)
)
[1] => Category Object
(
[id] => 2
[parent] => 0
[name] => Second Top
[children] => Array
(
[0] => Category Object
(
[id] => 6
[parent] => 2
[name] => Third Child
[children] => Array
(
)
)
[1] => Category Object
(
[id] => 7
[parent] => 2
[name] => Fourth Child
[children] => Array
(
)
)
)
)
[2] => Category Object
(
[id] => 3
[parent] => 0
[name] => Third Top
[children] => Array
(
)
)
)
Beispielnutzung
Ich würde vorschlagen, eine Art rekursive Funktion zu erstellen, wenn Sie Menüs aus den Daten erstellen möchten:
function outputCategories($categories, $startingLevel = 0)
{
$indent = str_repeat(" ", $startingLevel);
foreach ($categories as $category)
{
echo "$indent{$category->name}n";
if (count($category->children) > 0)
outputCategories($category->children, $startingLevel+1);
}
}
$categories = Category::getTopCategories();
outputCategories($categories);
was folgendes ausgeben würde:
First Top
First Child
First Subchild
Second Subchild
Second Child
Second Top
Third Child
Fourth Child
Third Top
Genießen
Ich fand die Antwort von icio toll, aber ich bevorzuge Arrays von Arrays statt Arrays von Objekten. Hier ist sein Skript, das so modifiziert wurde, dass es funktioniert, ohne Objekte zu erstellen:
<?php
require_once('mysql.php');
echo '<pre>';
$categories = Taxonomy::getTopCategories();
print_r($categories);
echo '</pre>';
class Taxonomy
{
public static function getTopCategories()
{
return self::getCategories('parent_taxonomycode_id = 0');
}
public static function getCategories($where="")
{
if ($where) $where = " WHERE $where";
$result = mysql_query("SELECT * FROM taxonomycode $where");
$categories = array();
// while ($category = mysql_fetch_object($result, 'Category'))
while ($category = mysql_fetch_array($result)){
$my_id = $category['id'];
$category['children'] = Taxonomy::getCategories("parent_taxonomycode_id = $my_id");
$categories[] = $category;
}
mysql_free_result($result);
return $categories;
}
}
Ich denke, es ist fair anzumerken, dass sowohl meine Antwort als auch icios Ihre Frage nicht direkt ansprechen. Sie verlassen sich beide darauf, dass es in der Haupttabelle einen Eltern-ID-Link gibt, und machen keinen Gebrauch von der Abschlusstabelle. Das rekursive Abfragen der Datenbank ist jedoch definitiv der richtige Weg, aber anstatt die übergeordnete ID rekursiv zu übergeben, müssen Sie die übergeordnete ID UND die Tiefe der Tiefe (die bei jeder Rekursion um eins zunehmen sollte) übergeben, damit die Abfragen auf jeder Ebene können Eltern + Tiefe verwenden, um die direkten Elterninformationen aus der Abschlusstabelle zu erhalten, anstatt sie in der Haupttabelle zu haben.
HTH, -FT
.
+1, für die Verwendung des Closure-Tabellenschemas.
– Alix Axel
8. Mai 10 um 15:54 Uhr
Danke Mann, lass es mich wissen, wenn du eine Idee hast, wie man ein großes, schönes und glänzendes Array daraus macht 🙂
– Industriell
8. Mai 10 um 16:04 Uhr
SQL weiß nichts über mehrdimensionale PHP-Arrays, daher müssen Sie einige Nachbearbeitungen im PHP-Code durchführen, wenn Sie Ergebnisse von der Abfrage zurückerhalten. Ich muss raus, aber ich werde später heute eine Antwort hinzufügen.
– Bill Karwin
8. Mai ’10 um 17:45 Uhr
Hallo Bill, das ist mir bekannt. Ich freue mich darauf, Ihren Ansatz dafür zu hören! Vielen Dank!
– Industriell
9. Mai ’10 um 16:05 Uhr