How do I aggregate results from an Adjacency list using PHP's SPL

Question

I've tried using nested sets, and they become very difficult to maintain when dealing with multiple trees and lots of other complications.. I'd like to give PHP's SPL library a stab at this (btw, we are PHP 5.3, MySQL 5.1).

Given two datasets:

The 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         |
+-------+--------+---------------------+---------------+

The 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 figures are actually much more complicated than this (I am actually aggregating a variable amount of months or years over the past 15yrs, so there may need to be 20 columns of aggregated results).

EDIT: @Gordon I suppose there are a number of ways to go about getting the resultset.. Ideally, I would like the RecursiveIterator stuff to give me something that I could spit into a view that would have all of the business logic (aggregation of leaf nodes, etc) done already.. so the result would look something like this (note that the depth in an 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 have been trying to figure out RecursiveIterator and IteratorAggregate, but I am having a difficult time finding real world examples that are generic enough to really wrap my head around these classes.

Can someone give me a head start?

EDIT:

A highly detailed solution is not needed here.. It just seems unclear where (if at all) I might utilize Iterator, RecursiveIterator, RecursiveIteratorIterator, etc. to come up with a clean, scalable solution to aggregate hierarchical data.

:END EDIT

Answer 1

What do you really want: nicer and shorter php code or more optimized and fast requests? If your case is the latter, you should really look into nested sets as they allow general operations on a tree (select all descendants, move branch, etc.) to be done by a single SQL request.

For concrete examples of implementation try google for "Zend_Db_NestedSet" proposal. It has support for "multi-root" trees, so you can theoretically use one table to keep generic hierarchical relations for different collections of data.