Cphalcon: PHQL Bug (critical)

[TimurFlush]

Hello.

I get a bug with PHQL in Phalcon 3.4

My code:

$query = $this->modelsManager->createQuery(<<<PHQL
        SELECT
          (SELECT COUNT(*) FROM [users] WHERE [created_at] <= [s].[date]) AS [direct]
        FROM
          (SELECT date_trunc('hour', [created_at]) AS [date], COUNT(*) AS [curve] FROM [users] WHERE [created_at] BETWEEN '2019-07-16' AND '2019-08-16' GROUP BY [date] ORDER BY [date] ASC) AS [s]
        PHQL);

        $query = $query->execute();

And on $query->execute() I'm catching the next error:

\Phalcon\Mvc\Model\Exception:
Syntax error, unexpected token (, near to 'SELECT date_trunc('hour', [created_at]) AS [date], COUNT(*) AS [curve] FROM [users] WHERE [created_at] BETWEEN '2019-07-16' AND '2019-08-16' GROUP BY [date] ORDER BY [date] ASC) AS [s]', when parsing: SELECT (SELECT COUNT(*) FROM [users] WHERE [created_at] <= [s].[date]) AS [direct] FROM (SELECT date_trunc('hour', [created_at]) AS [date], COUNT(*) AS [curve] FROM [users] WHERE [created_at] BETWEEN '2019-07-16' AND '2019-08-16' GROUP BY [date] ORDER BY [date] ASC) AS [s] (277)

[ruudboon]

Phalcon PHQL does not support subqueries right now. You can use left/right/inner joins or raw queries on the db adapter you're using.

[sergeyklay]

At the moment, there is very limited support for subqueries, and apparently your example is not included in this subset. I want to close this issue because I don't want a bunch of these lying around. I'd like to add support for anything/everything at some point, but keeping the issue open doesn't help that. :) If anyone wants to get started, I'd love that.