Я только что сделал обновить/добавить/удалить часть для "Таблицы закрытия" способом организовать запрос иерархические данные, которые показывают на странице 70 в этом slideshare: http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back
Моя база данных похожа на это:
Категории таблиц:
ID Name
1 Top value
2 Sub value1
Таблица CategoryTree:
child parent level
1 1 0
2 2 0
2 1 1
Однако у меня есть что-то вроде проблемы, возвращающей полное дерево как многомерный массив из единого запроса.
Вот то, что я хотел бы возвратить:
array (
'topvalue' = array (
'Subvalue',
'Subvalue2',
'Subvalue3)
);
);
Обновление: Найденный этой ссылкой, но мне все еще приходится нелегко для преобразования его в массив: http://karwin.blogspot.com/2010/03/rendering-trees-with-closure-tables.html
Update2: Я смог добавить глубины к каждой из категорий теперь, если это может иметь какую-либо справку.
Хорошо, я написал классы PHP, которые расширяют классы таблиц, строк и наборов строк Zend Framework DB. Я все равно это разрабатываю, потому что через пару недель я буду говорить на PHP Tek-X об иерархических моделях данных.
Я не хочу публиковать весь свой код в Stack Overflow, потому что они неявно получают лицензию Creative Commons, если я это сделаю. обновление: Я передал свой код в инкубатор Zend Framework extras , и моя презентация - Модели иерархических данных с SQL и PHP на слайд-шоу.
Я опишу решение в псевдокоде. Я использую зоологическую таксономию в качестве тестовых данных, загруженных с ITIS.gov . Таблица имеет вид longnames
:
CREATE TABLE `longnames` (
`tsn` int(11) NOT NULL,
`completename` varchar(164) NOT NULL,
PRIMARY KEY (`tsn`),
KEY `tsn` (`tsn`,`completename`)
)
Я создал закрывающую таблицу для путей в иерархии таксономии:
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`)
)
Учитывая первичный ключ одного узла, вы можете получить все его потомки следующим образом:
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;
Присоединение к закрытие AS p
должно включать идентификатор родительского узла каждого узла.
В запросе довольно хорошо используются индексы:
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+
| 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 | |
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+
И, учитывая, что у меня 490 032 строки в longnames
и 4 299 883 строки в закрытии
, он выполняется довольно быстро:
+--------------------+----------+
| 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 |
+--------------------+----------+
Теперь я пост-обрабатываю результат SQL-запроса выше, сортируя строки на подмножества в соответствии с иерархией (псевдокодом):
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;
Я также определяю классы для строк и наборов строк. Набор строк - это в основном массив строк. Строка содержит ассоциативный массив данных строки, а также содержит набор строк для своих дочерних элементов. Дочерний набор строк для листового узла пуст.
Строки и наборы строк также определяют методы, называемые toArrayDeep ()
, которые рекурсивно выгружают содержимое своих данных в виде простого массива.
Тогда я могу использовать всю систему вместе вот так:
// 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());
Результат будет следующим:
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',
),
),
),
),
)
Повторите ваш комментарий о вычислении глубины - или действительно длины каждого пути.
Предполагая, что вы только что вставили в таблицу новый узел, содержащий фактические узлы ( longnames
в приведенном выше примере), идентификатор нового узла возвращается функцией LAST_INSERT_ID ()
в MySQL, а то можно как-нибудь получить.
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;
Извините, но я не думаю, что вы сможете не получить многомерный массив из вашего (или любого) запроса к базе данных.
Следующий пример дает немного больше, чем вы просите, но это действительно хороший способ сделать это и все еще демонстрирует, откуда берется информация на каждом этапе.
Он использует следующую структуру таблицы:
+--------+------------------+------+-----+---------+----------------+
| 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 | |
+--------+------------------+------+-----+---------+----------------+
Вот она:
<?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;
}
}
В моей базе данных есть следующие строки:
+----+--------+-----------------+
| 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 |
+----+--------+-----------------+
И таким образом скрипт выводит следующую (длинную) информацию:
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
(
)
)
)
Я бы предложил создать какую-нибудь рекурсивную функцию, если вы собираетесь создавать меню из данных:
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);
которая выводила бы следующее:
First Top
First Child
First Subchild
Second Subchild
Second Child
Second Top
Third Child
Fourth Child
Third Top
Enjoy
Мне понравился ответ icio, но я предпочитаю иметь массивы массивов, а не массивов объектов. Вот его сценарий, измененный для работы без создания объектов:
<?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;
}
}
Я считаю справедливым отметить, что и мой ответ, и icios не касаются вашего вопроса напрямую. Оба они полагаются на ссылку родительского идентификатора в основной таблице и не используют таблицу закрытия. Тем не менее, рекурсивный запрос к базе данных - это определенно способ, но вместо рекурсивной передачи родительского идентификатора вы должны передать родительский идентификатор И уровень глубины (который должен увеличиваться на единицу при каждой рекурсии), чтобы запросы на каждом уровне можно использовать parent + depth для получения прямой родительской информации из закрывающей таблицы, а не в основной таблице.
HTH, -FT