Lazy Loading with Doctrine2 and Symfony2 using DQL
It looks like your are using the adjacency model for storing trees in a relational database. Which in turn means, that you will need a join for every level to get all ancestors with a single query.
As you are already using the Doctrine Extension Library I recommend to have a look at the Tree component.
My Answer involves not using DQL and instead creating a NestedSetManager which has access to your DBAL connection so you can use SQL. I never felt like the ORM's did a good job with NestedSets query logic.
With a NestedSetManager
, you can then write a bunch of clean methods and it's really simple because all these queries are well documented. See this link. Some of the method in my NestedSetManager are:
setNode();setRoot();loadNestedSet();moveNodeUp();modeNodeDown();getRootNode();addNodeSibling();getNodesByDepth();getParents();getNodePath();childExists();addChildToNode();renameNode();deleteNode();// And many more
You can have a ball and create a lot of create NestedSet functionality if you're not tied down by an ORM's somewhat complex functionality.
Also -- Symfony2 makes all this really really easy. You create your NestedSetManager class file and reference it in your Services.yml and pass in your Dbal connection. Mine looks like this:
services: manager.nestedset: class: Acme\CoreBundle\Manager\NestedSetManager arguments: [ @database_connection ]
you can then access your nestedsets with:
$path = $this->get('manager.nestedset')->setNode(4)->getNodePath(); // in your controller
Moral of the story, ORM/NestedSets drove me bonkers and this solution work really well. If you're being forced to use DQL and have no other options, this answer probably wont be acceptable.