I'm aware that subqueries are not supported by PHQL, but I would still like to find some way to put them into queries. I also know that I could simply use raw SQL, but then I would lose the convenience of query builder... Basically I'm looking for a way to embed some raw SQL into WHERE, for example, it could look like this:
$query->andWhereSQL('(SELECT COUNT(*) FROM ProductCategory WHERE categoryID=123 AND ProductCategory.productID=Product.ID) > 0');
So, instead of interpreting this as PHQL, it would be included in the generated query directly. This would be useful not only for subqueries, but specific database functions too.
I have tried looking at the getIntermediate() and setIntermediate() methods to see if I can somehow trick this in. Perhaps it even could use the full query builder functionality in a way like this:
$query->andWhere(':subquery: > 0', array('subquery' => array('type' => 'literal', 'condition' => '(SELECT COUNT(*) FROM ProductCategory WHERE categoryID=123 AND ProductCategory.productID=Product.ID)')));
Is there a way I can alter the intermediate data to implement this?