PHQL - Joining subquery

Is there any possible way to use PHQL and join a custom subquery to the resultset? Now PHQL throws the exception points right to the subquery. ```php $this->modelsManager->createBuilder() ->from('Robots') ->join('RobotsParts') ->join('(SELECT 0 as number)', null, 'p') ->orderBy('Robots.name') ->getQuery() ->execute(); ```

I face the same problem. The short answer is subqueries is not supported yet in PHQL The long answer: http://forum.phalconphp.com/discussion/147/how-to-perform-a-subquery-with-a-querybuilder-object- And also please add +1 to https://github.com/phalcon/cphalcon/issues/496

BTW What's you're trying to achieve by (SELECT 0 as number)? Do you want to add an extra column with value of zero? You can use ->columns('0 as number') right?



21.0k
BTW What's you're trying to achieve by (SELECT 0 as number)?

It's just an example without specific table structure or DBMS features. Don't you think I also use Robots and RobotsParts models in my app? =) Now I have found subqueries useful for counting related models (in one query as column, but not calling count() and new query for each of them).

Added +1 to subquery support.

You can laod related tables if you defined the relations in your models. Anothe way is to use raw sql.