How to aggregate Adjacency results using PHP SPL

Question

I tried using nested sets, and it is very difficult to maintain them when working with multiple trees and many other complications. I would like to give the PHP SPL library a shot at this (by the way, we are PHP 5.3, MySQL 5.1).

For two data sets:

Groups:

+-------+--------+---------------------+---------------+ | id | parent | Category Name | child_key | +-------+--------+---------------------+---------------+ | 11133 | 7707 | Really Cool Products| 47054 | | 7709 | 7708 | 24" Monitors | 57910 | | 7713 | 7710 | Hot Tubs | 35585 | | 7716 | 7710 | Hot Dogs | 00395 | | 11133 | 7707 | Really Cool Products| 66647 | | 7715 | 7710 | Suction Cups | 08396 | +-------+--------+---------------------+---------------+ 

Items

 +------------+------------+-----------+----------+---------+ | child_key | totalprice | totalcost | totalqty | onorder | (jan, feb, mar..) +------------+------------+-----------+----------+---------+ | 24171 | 10.50 | 20.10 | 200 | 100 | | 35685 | 10.50 | 20.10 | 200 | 100 | | 76505 | 10.50 | 20.10 | 200 | 100 | | 04365 | 10.50 | 20.10 | 200 | 100 | | 01975 | 10.50 | 20.10 | 200 | 100 | | 12150 | 10.50 | 20.10 | 200 | 100 | | 40060 | 10.50 | 20.10 | 200 | 100 | | 08396 | 10.50 | 20.10 | 200 | 100 | +------------+------------+-----------+----------+---------+ 

The numbers are actually much more complex than that (I actually collect a variable number of months or years over the past 15 years, so it may take 20 columns of aggregated results).

EDIT: @ Gordon. I believe there are several ways to get a set of results. Ideally, I would like the RecursiveIterator material to give me something that I could spit on a view that would have all the business logic (aggregation of leaf nodes, etc.) already. Therefore, the result will look something like this (note that the depth in the adjacency list is arbitrary):

 +------------+------------+-----------+----------+---------+ | Name | totalprice | totalcost | totalqty | onorder | (jan, feb, mar..) +------------+------------+-----------+----------+---------+ | Monitors | 36.00 | 60.40 | 800 | 400 | (category) | --24" | 22.00 | 40.20 | 400 | 200 | (category) | ---04365 | 10.50 | 20.10 | 200 | 100 | (item) | ---04366 | 11.50 | 20.10 | 200 | 100 | (item) | --22" | 1.50 | 10.10 | 200 | 100 | (category) | ---04365 | 1.50 | 10.10 | 200 | 100 | (item) | -01234 | 12.50 | 10.10 | 200 | 100 | (item) +------------+------------+-----------+----------+---------+ 

: END EDIT

I tried to figure out RecursiveIterator and IteratorAggregate , but it's hard for me to find real examples that are common enough to really wrap my head around these classes.

Can someone give me a head?

EDIT:

It does not require a very detailed solution. It's just not clear where (if at all) I can use Iterator, RecursiveIterator, RecursiveIteratorIterator, etc., to come up with a clean, scalable solution for combining hierarchical data.

: END EDIT

Answer 1

What do you really want: a more convenient and shorter PHP code or more optimized and faster queries? If your case is the latter, you should really look into nested sets, since they allow general tree operations (select all descendants, move a branch, etc.) that will be executed with a single SQL query.

For specific implementation examples, try google for the "Zend_Db_NestedSet" clause. It supports trees with several roots, so theoretically you can use one table to maintain common hierarchical relationships for different data collections.