[symfony-users] How to make LEFT JOIN in subquery which is in WHERE condition?

[MaEcTPo]

I wrote a raw SQL, like this:

SELECT b2.title, r.title
FROM route r
LEFT JOIN busstop_route b ON (r.id = b.route_id)
LEFT JOIN busstop b2 ON b2.id = b.busstop_id
WHERE b2.id <> 3
AND b.direction_id =
(
    SELECT ab.direction_id
    FROM route ar
    LEFT JOIN busstop_route ab ON (ar.id = ab.route_id)
    WHERE ab.busstop_id = 3
    AND r.id = ar.id
);

And I would like to write it in DQL. All goes good before subquery.

 $q = Doctrine_Query::create()
        ->from('Route r');
        $q->leftJoin('r.Directions d');
        $q->leftJoin('r.Busstops b');
        //$q->leftJoin('b.Places');
        $q->andWhere('b.id <> ?', $this->getId());
        $q->andWhere('d.id = (
    SELECT ab.direction_id
    FROM Route ar
    LEFT JOIN Busstops ab ON (ar.id = ab.route_id)
    WHERE ab.busstop_id = 3
    AND r.id = ar.id
)', '');

And here I receive strange errors. Symfony talks to me - "Couldn't
find class Busstops". But it has to find relation busstops (like in
main query), not class. When I change Busstops to Busstop I receive
"Unknown relation alias".

Do you understand what I need to do?

Please, help!

-- 
You received this message because you are subscribed to the Google Groups 
"symfony users" group.
To post to this group, send email to symfony-us...@googlegroups.com.
To unsubscribe from this group, send email to 
symfony-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/symfony-users?hl=en.