How to execute a subquery using PHQL?

Question

Phalcon

There is a request like this:

SELECT Messages.*
FROM (
    SELECT MAX(id) AS 'maxid', IF (author = 'Gildars', recipient, author) AS 'user'
    FROM Messages
    WHERE author = 'Gildars' or recipient = 'Gildars'
    GROUP BY user
) AS a
INNER JOIN Messages ON a.maxid = id
ORDER BY id DESC

I need to execute this request using PHQL.
Here is how I tried to execute it:

$this->modelsManager->executeQuery("SELECT Aincrad\Models\Messages.*
FROM (
    SELECT MAX(id) AS 'maxid', IF (author = 'Gildars', recipient, author) AS 'user'
    FROM Aincrad\Models\Messages
    WHERE author = 'Gildars' or recipient = 'Gildars'
    GROUP BY user
) AS a
INNER JOIN Aincrad\Models\Messages ON a.maxid = id
ORDER BY id DESC
 ");

But everything is deaf.
Mistake

Syntax error, unexpected token (, near to ' SELECT MAX(id) AS 'maxid', IF (author = 'Gildars', recipient, author) AS 'user' FROM Aincrad\Models\Messages WHERE author = 'Gildars' or recipient = 'Gildars' GROUP BY user ) AS a INNER JOIN Aincrad\Models\Messages ON a.maxid = id ORDER BY id DESC ', when parsing: SELECT Aincrad\Models\Messages.* FROM ( SELECT MAX(id) AS 'maxid', IF (author = 'Gildars', recipient, author) AS 'user' FROM Aincrad\Models\Messages WHERE author = 'Gildars' or recipient = 'Gildars' GROUP BY user ) AS a INNER JOIN Aincrad\Models\Messages ON a.maxid = id ORDER BY id DESC (314)

Answer 1

Subqueries in the from clause aren't supported, ho...
This, by the way, is quite logical: just ask yourself the question, how does Phalcon know what type of objects to create for your FROM?
If rewriting SQL is not possible, then use raw-sql with custom hydration.