There is such a request:

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 query 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. Here is a 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)
  • Instead of AS 'maxid' write AS maxid. What happens? - msi
  • Maybe he does not understand the subqueries in FROM. Transfer the subquery to where: select * from messages where id in(select max(id) from ... group by IF(author=....)) - Mike
  • It did not help, everything is also a mistake. - Gildars pm
  • Phalcon may not support subqueries. - Gildars

1 answer 1

PHQL does not support subqueries. In order to fulfill the request you must use PDO.

For example:

 $sql = " SELECT Messages.* FROM ( SELECT MAX(id) AS 'maxid', IF (author = :author, recipient, author) AS 'user' FROM Messages WHERE author = :author or recipient = :recipient GROUP BY user ) AS a INNER JOIN Messages ON a.maxid = id ORDER BY id DESC"; $messages = $this->db->query($sql,(array( "author" => $auth['login'], "recipient" => $auth['login'] ))); $messages->setFetchMode(\Phalcon\Db::FETCH_OBJ); $messages = $messages->fetchAll();