php - PhalconPHP 无法解析我的查询

Question

SELECT * FROM
(SELECT post.* FROM post ORDER BY post.timestamp DESC) as post
GROUP BY post.topic_id
ORDER BY post.timestamp DESC, post.position
LIMIT 15

Phalcon 在尝试执行上述查询时抛出以下错误。查询本身在默认 sql 上运行良好。

Syntax error, unexpected token (, near to ' SELECT post.* FROM post ORDER BY post.timestamp DESC ) as post GROUP BY post.topic_id ORDER BY post.timestamp DESC, post.position LIMIT 15', when parsing: SELECT * FROM ( SELECT post.* FROM post ORDER BY post.timestamp DESC ) as post GROUP BY post.topic_id ORDER BY post.timestamp DESC, post.position LIMIT 15 (162)

使用以下 PHP 代码(用于测试):

$sql = <<< QUERY
    *query*
QUERY;

$query = $this->modelsManager->createQuery($sql);
$result = $query->execute();

感谢您的帮助。

Answer 1

1. 创建 View ，以保留已经反向排序的虚拟表:

   CREATE VIEW v_latest_posts AS SELECT * FROM post ORDER BY timestamp DESC

2. 在选择中使用 View :

   从 v_latest_posts 中选择 *按 topic_id 分组ORDER BY 时间戳降序，位置升序限制 15