Left join in Doctrine Query Language

I want to apologies if I haven't done enough research in google but to be honest I don't know exactly what to search for. My problem is with Doctrine. I use Symfony framework and I want to write the following raw query in DQL:

SELECT c.id AS id,
c.name AS name,
c.active AS active,
count(c2.id) AS depth,
(c.rht - c.lft) as rng
FROM (categories c
LEFT JOIN categories c2 ON ( (c2.lft < c.lft) AND (c2.rht > c.rht) AND c2.active ) )
GROUP by c.id
ORDER by c.lft ASC

I can write it like a raw query, but I ne开发者_如何学运维ed it in DQL as I want to use it for a sfWidgetFormDoctrineChoice widget. Any help is highly appreciated.

Thanks in advance!

A possible approuch would be:

$query = Doctrine_Query::create()
  ->select('c.name AS name,
            c.active AS active,
            count(c2.id) AS depth,
            (c.rht - c.lft) as rng')
  ->from('categories c')
  ->leftJoin('c.Categories c2 ON ( (c2.lft < c.lft) AND (c2.rht > c.rht) AND c2.active')
  ->groupBy('c.id')
  ->orderBy('c.lft ASC');

But I think your on the wrong way. Did you check out the tree behavior of doctrine? http://www.doctrine-project.org/documentation/manual/1_0/nl/hierarchical-data#nested-set:advanced-usage:fetching-a-tree-with-relations