Zamień wynik bazy danych w tablicę
Właśnie zrobiłem część update / add/delete dla "Closure table" sposobu organizowania hierarchicznych danych zapytań, które są pokazane na stronie 70 w tym pokazie slajdów: http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back
Moja baza wygląda tak:
Kategorie Tabeli:
ID Name
1 Top value
2 Sub value1
Kategoria Tabeli:
child parent level
1 1 0
2 2 0
2 1 1
Jednak mam mały problem z odzyskaniem pełnego drzewa jako wielowymiarowej tablicy z jednego zapytanie.
Oto co chciałbym odzyskać:
array (
'topvalue' = array (
'Subvalue',
'Subvalue2',
'Subvalue3)
);
);
Aktualizacja: Znalazłem ten link, ale nadal mam problem z przekonwertowaniem go na tablicę: http://karwin.blogspot.com/2010/03/rendering-trees-with-closure-tables.html
Update2: Udało mi się teraz dodać głębię do każdej z kategorii, jeśli to może pomóc.
5 answers
Dobra, napisałem klasy PHP, które rozszerzają Zend Framework DB table, row i rowset. I tak to rozwijałem, ponieważ za kilka tygodni będę mówił na PHP tek-X o hierarchicznych modelach danych.
Nie chcę publikować całego kodu do Stack Overflow, ponieważ bezwarunkowo dostają licencję Creative Commons, jeśli to zrobię. update: przekazałem swój kod do Zend Framework extras incubator i moja prezentacja jest Modele danych hierarchicznych z SQL i PHP w slideshare.
Opiszę rozwiązanie w pseudokodzie. Używam taksonomii zoologicznej jako danych testowych, pobranych z ITIS.gov . tabela jest longnames
:
CREATE TABLE `longnames` (
`tsn` int(11) NOT NULL,
`completename` varchar(164) NOT NULL,
PRIMARY KEY (`tsn`),
KEY `tsn` (`tsn`,`completename`)
)
Stworzyłem tabelę zamknięcia dla ścieżek w hierarchii taksonomii:
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`)
)
Biorąc pod uwagę klucz podstawowy jednego węzła, można uzyskać wszystkie jego potomkinie w ten sposób:
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;
Dołączenie do {[10] } ma obejmować każdy identyfikator rodzica węzła.
Zapytanie bardzo dobrze wykorzystuje indeksy:
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+
| 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 | |
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+
I biorąc pod uwagę, że mam 490,032 wierszy w longnames
i 4,299,883 wierszy w closure
, Działa w całkiem dobrym czasie:
+--------------------+----------+
| 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 |
+--------------------+----------+
Teraz przetwarzam wynik powyższego zapytania SQL, sortując wiersze na podzbiory zgodnie z hierarchią (pseudokod):
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;
Definiuję również klasy dla wierszy i zestawów wierszy. Zestaw wierszy jest w zasadzie tablicą wierszy. Wiersz zawiera tablicę asocjacyjną danych wierszy, a także zawiera zestaw wierszy dla swoich dzieci. Zestaw wierszy dla dzieci dla węzła liścia jest pusty.
Wiersze i zestawy wierszy definiują również metody o nazwie toArrayDeep()
, które rekurencyjnie zrzucają zawartość danych jako zwykłą tablicę.
Wtedy mogę używać całego systemu razem tak:
// 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());
Wyjście jest następujące:
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',
),
),
),
),
)
Re twój komentarz o obliczaniu głębokości -- lub naprawdę długości każdej ścieżki.
Zakładając, że właśnie wstawiłeś nowy węzeł do tabeli przechowuje rzeczywiste węzły (longnames
w powyższym przykładzie), identyfikator nowego węzła jest zwracany przez LAST_INSERT_ID()
W MySQL, albo możesz go jakoś uzyskać.
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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-06-03 17:07:34
Proponowane Rozwiązanie
Poniższy przykład daje nieco więcej, niż prosisz, ale jest to naprawdę miły sposób na zrobienie tego i nadal pokazuje, skąd pochodzą informacje na każdym etapie.
Wykorzystuje następującą strukturę tabeli:
+--------+------------------+------+-----+---------+----------------+
| 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 | |
+--------+------------------+------+-----+---------+----------------+
Oto jest:
<?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;
}
}
Przypadek Testowy
W mojej bazie mam następujące wiersze:
+----+--------+-----------------+
| 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 |
+----+--------+-----------------+
I w ten sposób skrypt wyświetla następujące (długie) informacje:
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
(
)
)
)
Przykładowe Użycie
I ' D zaproponuj utworzenie jakiejś funkcji rekurencyjnej, jeśli masz zamiar utworzyć menu z danych:
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);
, co daje następujące wyjście:
First Top
First Child
First Subchild
Second Subchild
Second Child
Second Top
Third Child
Fourth Child
Third Top
Enjoy
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-05-08 18:10:17
Podobała mi się odpowiedź od icio, ale wolę mieć tablice tablic, a nie tablice obiektów. Oto jego skrypt zmodyfikowany do pracy bez tworzenia obiektów:
<?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;
}
}
Myślę, że należy zauważyć, że zarówno moja odpowiedź, jak i icios nie odnoszą się bezpośrednio do twojego pytania. Obie polegają na posiadaniu łącza ID nadrzędnego w tabeli głównej i nie wykorzystują tabeli zamknięcia. Jednak rekurencyjnie odpytywanie bazy danych jest zdecydowanie sposobem, ale zamiast rekurencyjnie przekazywać rodzica id, musisz przekazać ID rodzica i poziom głębi (który powinien wzrosnąć o jeden przy każdej rekurencji) tak, że zapytania na każdym poziomie mogą używać parent + głębokość, aby uzyskać bezpośrednie informacje rodzica z tabeli zamknięcia, zamiast mieć je w tabeli głównej.
HTH, - FT
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-06-03 11:24:08
Jeśli chcesz, aby wynik był listą nieuporządkowaną, możesz zmienić metodę outputCategories w następujący sposób (na podstawie tablic ftrotters w tablicach):
public function outputCategories($categories, $startingLevel = 0)
{
echo "<ul>\n";
foreach ($categories as $key => $category)
{
if (count($category['children']) > 0)
{
echo "<li>{$category['name']}\n";
$this->outputCategories($category['children'], $startingLevel+1);
echo "</li>\n";
}
else
{
echo "<li>{$category['name']}</li>\n";
}
}
echo "</ul>\n";
}
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-05-26 14:24:34
Przepraszam, ale nie sądzę, że nie można uzyskać wielowymiarową tablicę z (lub jakiekolwiek) zapytanie bazy danych.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-05-08 16:07:25