Search code examples
symfony1doctrinenested-setsdql

Fetching doctrine nested set with one query

I am looking a way to fetch a nested set in one db query.

schema.yml
Category:
  actAs:
    NestedSet:
      hasManyRoots: true
      rootColumnName: root_id
  columns:
    name: string(255)

$categories = Doctrine::getTable('Category')->getTree();

//SELECT * FROM category c ORDER BY c.root_id asc, c.lft asc

foreach ($categories as $cat)
{
  $cat->getNode(); 
//SELECT * FROM category c WHERE ((c.lft > '1' AND c.rgt < '8') AND c.root_id = '1') ORDER BY c.lft asc
}

So, in total I get as many queries as categories, which is bad. Any ideas?

Solution

  • Take a look at fetchTree()

    http://www.doctrine-project.org/api/orm/1.2/doctrine/doctrine_tree_nestedset.html#fetchTree%28%29

    http://www.doctrine-project.org/projects/orm/1.2/docs/manual/hierarchical-data%3Anested-set%3Aworking-with-trees/en#rendering-a-simple-tree