Whilst working on a recent project I needed the ability to do a subquery look-up as part of a select statement. Basically I needed to count the number of rows, without having to do a group by on the entire result set. After a few minutes of research I noticed there was no real definitive source on how to do do a subquery as a select using DQL, although there were some references to using a subquery within a where clause.

Below is a code snippet which worked for me:

$q = Doctrine_Query::create()
    ->select('t1.*')
    ->addSelect('t2.*');

// You can put this anywhere within the select clauses, but must be done before  you make call to from()        
$subQ = $q->createSubquery()
    ->select('COUNT(*)')
    ->from('Table3 t3')
    ->innerJoin('t3.Table4 t4')
    ->innerJoin('t4.Table5 t5')            
    ->where('t4.status_id = :statusId')
    ->andWhere('t5.id = t2.code_id');
        
$q->addSelect(sprintf('(%s) AS my_count', $subQ->getDql()))
    ->from('Table1 t1')
    ->innerJoin('t1.Table2 t2')
    ->where('t2.user_id = :userId', array('statusId' => $statusId, 'userId' => $userId);

// And you should now be able to access the count value using:
$result = $q->execute();

var_dump($result->my_count);

// Depending on how you have your models setup, you may have to do this instead:
var_dump($result->getFirst()->getMyCount();

Note, the table names are abstract for obvious reasons, but the snippet should demonstrate the ability to add a select subquery to a dql statement.

Here’s another example usage:

$q = Doctrine_Query::create()
    ->select('uh1.*');
        
$subQ = $q->createSubquery()
    ->select('uh2.user_id')
    ->from('UserHash uh2')
    ->where('uh2.hash = :hash');
        
$q->addSelect(sprintf('(%s) AS derived_user_id', $subQ->getDql()))
    ->from('UserHash uh1')            
    ->where('uh1.type_id = :typeId')
    ->having('uh1.user_id = derived_user_id', array('hash' => $hash, 'typeId' => $typeId));
        
return $q->execute();