Как найти все идентификаторы детей рекурсивно?
Я хотел бы получить все ID из дочерних элементов в дереве только с MySQL.
У меня есть таблица вроде этого:
ID parent_id name
1 0 cat1
2 1 subcat1
3 2 sub-subcat1
4 2 sub-subcat2
5 0 cat2
Теперь я пытаюсь получить все дочерние ID для cat1 (2,3,4) рекурсивно. Есть ли способ достижения этого?
Ответы
Ответ 1
Для этого существуют два основных метода: списки смежности и вложенные списки. Посмотрите Управление иерархическими данными в MySQL.
У вас есть список смежности. Нет никакого способа рекурсивного захвата всех потомков с помощью одного оператора SQL. Если возможно, просто возьмите их всех и нарисуйте их в коде.
Вложенные наборы могут делать то, что вы хотите, но я стараюсь избегать этого, потому что стоимость вставки записи высокая и она подвержена ошибкам.
Ответ 2
Вот простой однозадачный MySql-решение:
SELECT GROUP_CONCAT(Level SEPARATOR ',') FROM (
SELECT @Ids := (
SELECT GROUP_CONCAT(`ID` SEPARATOR ',')
FROM `table_name`
WHERE FIND_IN_SET(`parent_id`, @Ids)
) Level
FROM `table_name`
JOIN (SELECT @Ids := <id>) temp1
) temp2
Просто замените <id>
на родительский элемент ID
.
Это вернет строку с ID
всех потомков элемента с ID
= <id>
, разделенных ,
. Если вы предпочитаете возвращать несколько строк с одним потомком в каждой строке, вы можете использовать что-то вроде этого:
SELECT *
FROM `table_name`
WHERE FIND_IN_SET(`ID`, (
SELECT GROUP_CONCAT(Level SEPARATOR ',') FROM (
SELECT @Ids := (
SELECT GROUP_CONCAT(`ID` SEPARATOR ',')
FROM `table_name`
WHERE FIND_IN_SET(`parent_id`, @Ids)
) Level
FROM `table_name`
JOIN (SELECT @Ids := <id>) temp1
) temp2
))
Включение корневого/родительского элемента
ОП попросил детей элемента, на который дан ответ выше. В некоторых случаях может оказаться полезным включить в результат корневой/родительский элемент. Вот мои предлагаемые решения:
Линия идентификаторов, разделенных запятыми:
SELECT GROUP_CONCAT(Level SEPARATOR ',') FROM (
SELECT <id> Level
UNION
SELECT @Ids := (
SELECT GROUP_CONCAT(`ID` SEPARATOR ',')
FROM `table_name`
WHERE FIND_IN_SET(`parent_id`, @Ids)
) Level
FROM `table_name`
JOIN (SELECT @Ids := <id>) temp1
) temp2
Несколько строк:
SELECT *
FROM `table_name`
WHERE `ID` = <id> OR FIND_IN_SET(`ID`, (
SELECT GROUP_CONCAT(Level SEPARATOR ',') FROM (
SELECT @Ids := (
SELECT GROUP_CONCAT(`ID` SEPARATOR ',')
FROM `table_name`
WHERE FIND_IN_SET(`parent_id`, @Ids)
) Level
FROM `table_name`
JOIN (SELECT @Ids := <id>) temp1
) temp2
))
Ответ 3
Возможно, вы можете сделать это с помощью хранимой процедуры, если это вам вариант.
В противном случае вы не сможете сделать это с помощью одного SQL-оператора.
В идеале вы должны сделать рекурсивные вызовы, чтобы ходить по дереву из вашей программы
Ответ 4
создать таблицу, она должна выглядеть ниже
DROP TABLE IF EXISTS `parent_child`;
CREATE TABLE `parent_child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
insert into `parent_child`(`id`,`name`,`parent_id`)
values (1,'cat1',0),(2,'subcat1',1),
(3,'sub-subcat1',2),(4,'sub-subcat2',2),
(5,'cat2',0);
Создать функцию для получения родительского дочернего элемента
DELIMITER $$
USE `yourdatabase`$$
DROP FUNCTION IF EXISTS `GetAllNode1`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `GetAllNode1`(GivenID INT) RETURNS TEXT CHARSET latin1
DETERMINISTIC
BEGIN
DECLARE rv,q,queue,queue_children TEXT;
DECLARE queue_length,front_id,pos INT;
SET rv = '';
SET queue = GivenID;
SET queue_length = 1;
WHILE queue_length > 0 DO
SET front_id = queue;
IF queue_length = 1 THEN
SET queue = '';
ELSE
SET pos = LOCATE(',',queue) + 1;
SET q = SUBSTR(queue,pos);
SET queue = q;
END IF;
SET queue_length = queue_length - 1;
SELECT IFNULL(qc,'') INTO queue_children
FROM (SELECT GROUP_CONCAT(id) AS qc
FROM `parent_child` WHERE `parent_id` = front_id) A ;
IF LENGTH(queue_children) = 0 THEN
IF LENGTH(queue) = 0 THEN
SET queue_length = 0;
END IF;
ELSE
IF LENGTH(rv) = 0 THEN
SET rv = queue_children;
ELSE
SET rv = CONCAT(rv,',',queue_children);
END IF;
IF LENGTH(queue) = 0 THEN
SET queue = queue_children;
ELSE
SET queue = CONCAT(queue,',',queue_children);
END IF;
SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
END IF;
END WHILE;
RETURN rv;
END$$
DELIMITER ;
написать запрос для выхода желания
SELECT GetAllNode1(id) FROM parent_child
or
SELECT GetAllNode1(id) FROM parent_child where id =1 //for specific parent child element
Ответ 5
Увидев, что ответ в принципе не является или, по крайней мере, не очень легким с помощью одного оператора MYSQL, я отправлю свой код php/mysql, чтобы сделать список иерархии.
function createCategorySubArray()
{
$categories = getSQL("SELECT pos_category_id FROM pos_categories");
for($i=0;$i<sizeof($categories);$i++)
{
//here we need to find all sub categories
$pos_category_id = $categories[$i]['pos_category_id'];
$cat_list[$pos_category_id] = recursiveCategory($pos_category_id,array());
}
return $cat_list;
}
function recursiveCategory($pos_category_id, $array)
{
$return = getSql("SELECT pos_category_id FROM pos_categories WHERE parent = $pos_category_id");
for($i=0;$i<sizeof($return);$i++)
{
$sub_cat = $return[$i]['pos_category_id'];
$array[] = $sub_cat;
$array = recursiveCategory($sub_cat, $array);
}
return $array;
}
Затем вы вызываете его
$ cat_array = createCategorySubArray();
Мне нужно это, чтобы узнать, какие рекламные акции, основанные на категориях продуктов, применяются к подкатегориям.
Ответ 6
Ваш вопрос кажется немного неточным. Почему вы хотите иметь их, и что вы имеете в виду, имея их, "на дереве"?
В таблице у вас есть IS (реляционный способ представления) дерева.
Если вы хотите, чтобы они были "в таблице" с строками, содержащими пары (ID 4, ParentID 0), для этого вам нужна версия рекурсивного SQL для SQL-сервера, если этот движок поддерживает ее.
Я бы не знал о MySQL конкретно, но я понимаю, что они когда-то планировали реализовать рекурсивный SQL, используя тот же синтаксис, что и Oracle, то есть с CONNECT BY.
Если вы посмотрите в своей таблице оглавления для ключевых слов, таких как "рекурсивные запросы" или "CONNECT BY", я думаю, вы должны найти ответ.
(Извините за невозможность предоставить более готовый к употреблению ответ.)