Превратить результат базы данных в массив
Я только что выполнил обновление / добавление / удаление для "таблицы закрытия" для организации иерархических данных запросов, которые показаны на странице 70 в этом слайд-шоу: 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
Обновление 2: я смог добавить глубины для каждой категории сейчас, если это может помочь.
5 ответов
Хорошо, я написал PHP-классы, которые расширяют таблицы, строки и наборы строк Zend Framework DB. В любом случае, я занимаюсь этим, потому что через пару недель я говорю о PHP Tek-X об иерархических моделях данных.
Я не хочу публиковать весь свой код в Stack Overflow, потому что если я это сделаю, он неявно лицензируется в Creative Commons. обновление: я отправил свой код в инкубатор дополнений Zend Framework, и моя презентация - " Модели для иерархических данных с 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;
Присоединение к closure 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 строк в closureработает в довольно хорошее время:
+--------------------+----------+
| 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;
Я также определяю классы для строк и наборов строк. Rowset - это в основном массив строк. Строка содержит ассоциативный массив данных строки, а также содержит набор строк для своих дочерних элементов. Rowset дочерних элементов для конечного узла пуст.
Строки и Rowsets также определяют методы, называемые 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',
),
),
),
),
)
Re ваш комментарий о расчете глубины - или действительно длина каждого пути.
Предполагая, что вы только что вставили новый узел в вашу таблицу, который содержит фактические узлы (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
наслаждаться
Мне понравился ответ от 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 + глубина, чтобы получить непосредственную родительскую информацию из таблицы замыкания, а не иметь ее в основной таблице.
HTH, -FT
Если вы хотите, чтобы вывод был неупорядоченным списком, вы можете изменить метод outputCategories следующим образом (на основе массивов ftrotters в массивах):
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";
}
Извините, но я не думаю, что вы не можете получить многомерный массив из вашего (или любого) запроса к базе данных.