Рекурсивный поиск дерева MySQL

Вы действительно должны рассмотреть возможность использования измененного обхода дерева заказов, который упрощает такие запросы. Вот ваш стол, выраженный с помощью MPTT. Я покинул родительское поле, так как он упрощает некоторые запросы.

 +----------------------+-----+------+ | id | name | parent | lft | rght | +----------------------+-----+------+ | 1 | tom | 0 | 1 | 6 | | 2 | bob | 0 | 7 | 18 | | 3 | fred | 1 | 2 | 5 | | 4 | tim | 2 | 8 | 17 | | 5 | leo | 4 | 12 | 15 | | 6 | sam | 4 | 9 | 16 | | 7 | joe | 6 | 10 | 11 | | 8 | jay | 3 | 3 | 4 | | 9 | jim | 5 | 13 | 14 | +----------------------+-----+------+ 

Для поиска j из пользовательского bob вы использовали значения rght и rght для bob :

 SELECT * FROM table WHERE name LIKE 'j%' AND lft > 7 AND rght < 18 

Реализация логики для обновления rght и rght для добавления, удаления и переупорядочения узлов может быть проблемой (подсказка: используйте существующую библиотеку, если можете), но запрос будет ветерок.

Существует не хороший / простой способ сделать это; базы данных не поддерживают древовидные структуры данных.

Вам нужно будет работать на уровне уровня, чтобы обрезать результаты от дочернего к родительскому, или создать представление, которое дает все 9 поколений из данного узла, и сопоставлять с использованием OR для потомков.

Вы думали об использовании рекурсивного цикла? Я использую цикл для cms i, созданный на основе codeigniter, который позволяет мне начинать в любом месте дерева сайта, а затем потом фильтровать все дети> грандиозные дети> великих детей и т. д. Кроме того, он удерживает sql до коротких быстрых запросы против множества сложных объединений. В вашем случае может потребоваться некоторое изменение, но я думаю, что это может сработать.

 /** * build_site_tree * * @return void * @author Mike Waites **/ public function build_site_tree($parent_id) { return $this->find_children($parent_id); } /** end build_site_tree **/ // ----------------------------------------------------------------------- /** * find_children * Recursive loop to find parent=>child relationships * * @return array $children * @author Mike Waites **/ public function find_children($parent_id) { $this->benchmark->mark('find_children_start'); if(!class_exists('Account_model')) $this->load->model('Account_model'); $children = $this->Account_model->get_children($parent_id); /** Recursively Loop over the results to build the site tree **/ foreach($children as $key => $child) { $childs = $this->find_children($child['id']); if (count($childs) > 0) $children[$key]['children'] = $childs; } return $children; $this->benchmark->mark('find_children_end'); } /** end find_children **/ 

Как вы видите, это довольно простая версия, и имейте в виду, что это было встроено в codeigniter, поэтому вам нужно будет модифицировать его в наборе, но в основном у нас есть цикл, который называет себя добавлением в массив каждый раз по мере его появления. Это позволит вам получить все дерево или даже начать с точки в дереве, если у вас есть родительский avaialble в первую очередь!

Надеюсь это поможет

Новая конструкция «recursive with» выполнит эту работу, но я не знаю, поддерживает ли ее MySQL MySQL (пока).

 with recursive bobs(id) as ( select id from t where name = 'bob' union all select t.id from t, bobs where t.parent_id = bobs.id ) select t.name from t, bobs where t.id = bobs.id and name like 'j%' 

Не существует единого SQL-запроса, который будет возвращать данные в древовидном формате – вам нужна обработка, чтобы пройти в нужном порядке.

Один из способов – запросить MySQL для возврата MPTT:

SELECT * FROM table ORDER BY parent asc;

корень дерева будет первым элементом таблицы, его дочерние элементы будут следующими и т. д., причем дерево будет указано «ширина вначале» (в слоях с увеличением глубины)

Затем используйте PHP для обработки данных, превращая их в объект, который содержит структуру данных.

Кроме того, вы можете реализовать функции поиска MySQL, которые задают узел, рекурсивно искать и возвращать таблицу всех его потомков или таблицу всех ее предков. Поскольку эти процедуры имеют тенденцию быть медленными (рекурсивными, возвращая слишком много данных, которые затем фильтруются по другим критериям), вы хотите сделать это только в том случае, если знаете, что вы не запрашиваете данные такого типа снова и снова, или если вы знаете, что набор данных остается небольшим (9 уровней глубокие и насколько широкие?)

Вы можете сделать это с помощью хранимой процедуры следующим образом:

Примеры вызовов

 mysql> call names_hier(1, 'a'); +----+----------+--------+-------------+-------+ | id | emp_name | parent | parent_name | depth | +----+----------+--------+-------------+-------+ | 2 | ali | 1 | f00 | 1 | | 8 | anna | 6 | keira | 4 | +----+----------+--------+-------------+-------+ 2 rows in set (0.00 sec) mysql> call names_hier(3, 'k'); +----+----------+--------+-------------+-------+ | id | emp_name | parent | parent_name | depth | +----+----------+--------+-------------+-------+ | 6 | keira | 5 | eva | 2 | +----+----------+--------+-------------+-------+ 1 row in set (0.00 sec) $sqlCmd = sprintf("call names_hier(%d,'%s')", $id, $name); // dont forget to escape $name $result = $db->query($sqlCmd); 

Полный скрипт

 drop table if exists names; create table names ( id smallint unsigned not null auto_increment primary key, name varchar(255) not null, parent smallint unsigned null, key (parent) ) engine = innodb; insert into names (name, parent) values ('f00',null), ('ali',1), ('megan',1), ('jessica',3), ('eva',3), ('keira',5), ('mandy',6), ('anna',6); drop procedure if exists names_hier; delimiter # create procedure names_hier ( in p_id smallint unsigned, in p_name varchar(255) ) begin declare v_done tinyint unsigned default(0); declare v_dpth smallint unsigned default(0); set p_name = trim(replace(p_name,'%','')); create temporary table hier( parent smallint unsigned, id smallint unsigned, depth smallint unsigned )engine = memory; insert into hier select parent, id, v_dpth from names where id = p_id; /* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */ create temporary table tmp engine=memory select * from hier; while not v_done do if exists( select 1 from names n inner join tmp on n.parent = tmp.id and tmp.depth = v_dpth) then insert into hier select n.parent, n.id, v_dpth + 1 from names n inner join tmp on n.parent = tmp.id and tmp.depth = v_dpth; set v_dpth = v_dpth + 1; truncate table tmp; insert into tmp select * from hier where depth = v_dpth; else set v_done = 1; end if; end while; select n.id, n.name as emp_name, p.id as parent, p.name as parent_name, hier.depth from hier inner join names n on hier.id = n.id left outer join names p on hier.parent = p.id where n.name like concat(p_name, '%'); drop temporary table if exists hier; drop temporary table if exists tmp; end # delimiter ; -- call this sproc from your php call names_hier(1, 'a'); call names_hier(3, 'k');