SELECT statement in a LEFT JOIN statement with DOCTRINE in SYMFONY?

Is there any way to run this sql query in Doctrine using symfony ??

$q = "SELECT a.id as app_id, b.title as gameTitle FROM application a 
开发者_运维知识库    LEFT JOIN application_translation b on a.id=b.id 
    LEFT OUTER JOIN (SELECT m.application_id as m_id, count(m.member_id) as total
    FROM member_application m GROUP BY m.application_id) x on x.m_id = a.id 
    WHERE a.is_active=1 AND a.is_mobile = 1
    ORDER BY x.total DESC";

Update

Thanks for your reply. Just one thing, how could I convert

object(sfOutputEscaperArrayDecorator)#524

to

object(sfOutputEscaperIteratorDecorator)#560

by using your code? I think that's because of HYDRATE MODE, but I dont get it clearly.

You can try to execute your raw SQL like that:

$q = "SELECT a.id as app_id, b.title as gameTitle FROM application a 
LEFT JOIN application_translation b on a.id=b.id 
LEFT OUTER JOIN (SELECT m.application_id as m_id, count(m.member_id) as total
FROM member_application m GROUP BY m.application_id) x on x.m_id = a.id 
WHERE a.is_active=1 AND a.is_mobile = 1
ORDER BY x.total DESC";
$doctrine = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
$result = $doctrine->query($q);