Поиск лучшего родителя (рекурсивный)
Таблица продуктов:
id | id_parent | margin
------------------------
1 | NULL | 10
2 | 1 | 20
3 | NULL | 15
4 | 2 | 10
5 | 4 | 25
Из данных выше, как получить маржу всего дерева продуктов (id: 5)? Он не имеет фиксированной глубины, он динамический.
В настоящее время я достигаю этого с помощью итераций из PHP (рекурсивный mysqli_query), но он потребляет огромную память хостинга (ошибка 508).
Вот функция PHP:
function get_margintotal($product) {
global $link;
$query = "SELECT id_parent, margin FROM `products` WHERE id = ".$product['id_parent'];
if (!$temp_products = mysqli_query($link, $query)) {
die(json_encode(array("errmsg" => "Selecting supplier's margin.")));
}
$temp_product = mysqli_fetch_assoc($temp_products);
$temp_product['margin'] = $product['margin'] + $temp_product['margin'];
if (isset($temp_product['id_parent'])) {
return get_margintotal($temp_product);
}
else {
return $temp_product['margin'];
}
}
Поэтому мне интересно, можно ли выполнить динамическую итерацию в MySQL или как оптимизировать функцию, чтобы уменьшить использование памяти сервером?
Спасибо.
Решение
MySQL решение с использованием рекурсии с переменными.
CREATE TABLE `finding_top_parent_recursive_46170847` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`id_parent` int(11) DEFAULT NULL,
`margin` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;INSERT INTO `finding_top_parent_recursive_46170847` (`id`, `id_parent`, `margin`)
VALUES
(1, NULL, 10),
(2, 1, 20),
(3, NULL, 15),
(4, 2, 10),
(5, 4, 25);-- set the seed leaf id
set @id = 5 ;
select sum(margin) margin from (
-- include the seed
select id, margin from finding_top_parent_recursive_46170847 products
where id = @id
union all
-- get the parents
select @id := (
select id_parent
from finding_top_parent_recursive_46170847 products
where id = @id ) id,
( select margin
from finding_top_parent_recursive_46170847 products
where id = @id ) margin
from
finding_top_parent_recursive_46170847 products
where @id is not null
) margins
-- results
margin
65
Идеи для рекурсивных MySQL результатов из
https://www.google.com.au/search?q=mysql+recursive+left+join
https://dba.stackexchange.com/questions/46127/recursive-self-joins
Другие решения
Если вы знаете максимальную глубину, скажем, 5, вы можете посчитать сумму в одном запросе с 4 LEFT JOINS:
SELECT p1.margin + IFNULL(p2.margin, 0) + IFNULL(p3.margin, 0) + IFNULL(p4.margin, 0) + IFNULL(p5.margin, 0)
FROM product AS p1
LEFT JOIN product AS p2 ON p1.id_parent = p2.id
LEFT JOIN product AS p3 ON p2.id_parent = p3.id
LEFT JOIN product AS p4 ON p3.id_parent = p4.id
LEFT JOIN product AS p5 ON p4.id_parent = p5.id
WHERE p1.id = :givenId
detector