Catharsis wrote:
Now I know this is probably not the most efficient way to do things but I can't figure out just why this bit of code is using up the 64Mb Ive allocated to Zend.
I don't know for sure why it's exhausting your memory, but 64MB is too little memory for any modern web app. The default setting for max memory in current versions of PHP is 128MB. But I can offer some suggestions regarding inefficiency.
You fetched the whole collection of category data before the loop, and you even sorted it by level, but you aren't taking advantage of this. You are preparing and executing extra SQL queries inside the loop when you don't have to (at least you should prepare outside the loop and execute with parameters inside the loop).
Your second code sample even creates a new instance of the table object during each iteration of the loop. This is an expensive operation to put inside a loop.
Here, try something like this:
public function breadcrumb()
{
$c = new Category();
$select = $c->select()->order( 'level' );
$rowset = $c->fetchAll( $select );
$hash = array();
foreach ($rowset as $row)
{
// keep a running index of each row by its id
$hash[$row->id] = $row;
// since we're processing rows in level order,
// we know that we've seen the parent already
$parent = $hash[$row->parent_id];
$breadcrumb = array();
if ($row != $parent)
{
// the parent's breadcrumb is guaranteed to be set,
// since we've processed the tree in level order
$breadcrumb[] = $parent->breadcrumb;
}
// append the current row's name to the array and join it
// to the breadcrumb we got from the parent
$breadcrumb[] = $row->name;
$row->breadcrumb = implode( ' > ', $breadcrumb);
// save the current row's breadcrumb and end the loop
$row->save();
}
}
There's also a way to store trees in an efficient manner, so it won't be so costly to produce the breadcrumb on the fly.
CREATE TABLE CategoryAdjacency (
ancestor INTEGER NOT NULL REFERENCES Category(id),
descendant INTEGER NOT NULL REFERENCES Category(id),
pathlength INTEGER NOT NULL,
PRIMARY KEY (ancestor, descendant)
);
Insert into this table every path through the tree, including paths of length zero.. For example, if A->B and B->C, then insert (A, A, 0), (A, B, 1), (A, C, 2), (B, B, 0), (B, C, 1), (C, C, 0). Then you can query a breadcrumb for C in a single query like this:
SELECT GROUP_CONCAT(c.name ORDER BY a.pathlength DESC SEPARATOR ' > ')
FROM Category AS c JOIN CategoryAdjacency AS a ON c.id = a.ancestor
WHERE a.descendant = 'C';
If you are using MySQL prior to version 4.1 or if you use another brand of database, you can't use GROUP_CONCAT(). Here's a workaround:
SELECT c.name
FROM Category AS c JOIN CategoryAdjacency AS a ON c.id = a.ancestor
WHERE a.descendant = 'C'
ORDER BY a.pathlength DESC;
Fetch with fetchCol() and implode the result in PHP code using ' > ' as the separator.
Regards,
Bill Karwin
