Posts tagged dql
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();