We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

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.

$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: https://forum.phalcon.io/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?



32.5k
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.