Subqueries with Doctrine_RawSql

Question

Is it possible to have subqueries in the select field of Doctrine_RawSql?

$q->select('{t.*}, {i.*}, {e.*}, {f.*}, {f2.*}');
$q->addSelect("(SELECT f.id FROM Following f WHERE f.follower_id = ? AND f.following_id = t.owner_id) AS following");
$q->addSelect("(SELECT COUNT(c.id) FROM PostComments c WHERE c.post_id = t.id) AS num_comments");

The above example is what I have tired, but it pretty much breaks the query (it will not select anything other than the primary keys on each row).

Is there a special way to do this or am I just out of luck?

Answer 1

When I have a complex query that's a bit too difficult for DQL I generally just grab the PDO object and enter a query manually. Here's how:

$PDO = Doctrine_Manager::getInstance()->connection()->getDbh();
$PDO->prepare("
    //SQL Query Here
")->execute();

Saves alot of time and the end result is probably also a bit more understandable.

Answer 2

I actually fixed this problem by modifying Doctrine_RawSql's parseDqlQueryPart function into accepting a parameter, "select_sql" (instead of just "select"), and rendering it as if it were DQL. This allows me to use Doctrine instead of PDO.