How can I fetch the entire tree in a single query with doctrine?

Question

This snippet from official website works as expected:

$treeObject = Doctrine::getTable('Category')->getTree();
$rootColumnName = $treeObject->getAttribute('rootColumnName');

foreach ($treeObject->fetchRoots() as $root) {
  $options = array(
      'root_id' => $root->$rootColumnName
  );
  foreach($treeObject->fetchTree($options) as $node) {
    echo str_repeat(' ', $node['level']) . $node['name'] . "\n";
  }
}

But I see there are dozens of queries sent to database.

How can I do it in a mouthful?

Answer 1

You can access the underlying table like a normal table in Doctrine with a query built with getBaseQuery(). The API lacks a description for this method, but check the source code:

public function getBaseQuery()
{
    if ( ! isset($this->_baseQuery)) {
        $this->_baseQuery = $this->_createBaseQuery();
    }
    return $this->_baseQuery->copy();
}

// ...

private function _createBaseQuery()
{
    $this->_baseAlias = "base";
    $q = Doctrine_Core::getTable($this->getBaseComponent())
        ->createQuery($this->_baseAlias)
        ->select($this->_baseAlias . '.*');
    return $q;
}

Answer 2

Just fetch the entire table with a DQL query before looking at the tree. Doctrine is smart enough to see that it has already loaded the tree nodes.

E.g.

$q = Doctrine_Query::create()->from('Category c')->orderBy('c.level');
$categories = $q->execute();

If you have n trees in your table (i.e. n root nodes), then the first n objects in $categories are your tree roots.

Depending on your use case, you may want to sort or hydrate differently. For example, to print out a tree (like in your example), you can do this:

$q = Doctrine_Query::create()
     ->select('c.name, c.level')
     ->from('Category c');
     ->orderBy('c.lft')
     ->setHydrationMode(Doctrine::HYDRATE_SCALAR);

$categories = $q->execute();

foreach ($categories as $category) {
    print str_repeat(' ', $category['c_level']) . $category['c_name'] . "\n";
}

That results in a single query.

Answer 3

Doesn't this do the trick?

$treeObject = Doctrine::getTable('Category')->getTree();
$tree = $treeObject->fetchTree();

foreach ($tree as $node) {
    echo str_repeat('  ', $node['level']) . $node['name'] . "\n";
}

Answer 4

Try this:

Doctrine_Core::getTable('YourTableWithTree')->createQuery('tree')
    ->addOrderBy('tree.root_id ASC')
    ->addOrderBy('tree.lft ASC');