Подсчет всех позиций вложенных категорий дерева Adjacency list?

Question

[Василий]

Из БД MySQL 5 (MyISAM) получаю из 2-х таблиц (категории и товары) таблицу вида:
id_category; parent_id_category; count_goods
Где count_goods - кол-во товаров соотв. категории.

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

При необходимости - можно вывести столбец с уровнем вложенности.

Answer 1

[Василий]

В моем случае частный случай решения проблемы, ввиду того, что таблица категорий заполняется
из файла *.xml, где категории вложены друг в друга.
Заполнив таблицу БД в том же порядке категорий как и в *.xml, я ее получаю, переворачиваю сверху вниз,
и считаю в один проход примерно следующим образом:

$res = $DBH->query("SELECT groups as id, (SELECT pid FROM cat_groups WHERE id = groups) as pid, count(groups) AS col FROM cat_prod WHERE visible = 1 
	AND id IN(SELECT DISTINCT prod_id FROM cat_prod_prop)
	GROUP BY groups");
	
	$res = $res->fetchAll(PDO::FETCH_ASSOC);
	$res = array_reverse($res);
	
	foreach ($res as $k => $v) {
		$keys = array_keys(array_column($res, 'pid'), $v['id']);
		foreach ($keys as $w) {
			$res[$k]['col'] += $res[$w]['col'];
		}
	}
	foreach ($res as $row) {
		$STH = $DBH->prepare("UPDATE cat_groups SET count = :count WHERE id = :id");
		$STH->bindParam(':count', $row['col']);
		$STH->bindParam(':id', $row['id']);
		$STH->execute();
	}

Решение не универсальное и подойдет если будем работать с результатом запроса в виде adjacency list, у которого потомки идут последовательно, учитывая уровень вложенности.

Пример:

id;pid;count
1;0;34
2;1;11
3;1;25
4;3;12
5;3;20
6;0;97
7;6;36
8;6;25
9;0;15