如何通过最新的帖子或评论(如果有的话）获取所有带有评论顺序的帖子-zendframework2php-mysql

我想显示所有帖子的列表。如果为某个特定帖子创建了新的评论，那么该帖子将位于列表的顶部。

这是我在模型中的函数

   public function fetchAll($data) {
    $sql = new Sql($this->adapter);
    $where = new Where();
    $select = new Select();
    $this->table = "threads";
    $select->from($this->table);
    $select->join('thread_replies', 'thread_replies.thread_id = ' . $this->table . '.thread_id', array('modifiedReply' => "modified"), "left");
    $select->join(array('b' => 'buildings'), 'b.id = threads.building_id', array('building_name'));
    $select->join('user', 'user.user_id = threads.created_by', array('display_name', 'username'));
    if ($data['userLevel'] == 2) {
        $select->where($where->equalTo($this->table . '.building_id', $data['building_id']));
    }
    if (isset($data['building'])) {
        $select->where($where->equalTo($this->table . '.building_id', $data['building']));
    }
    if (isset($data['name'])) {
        $select->where($where->Like($this->table . '.name', $data['name'] . '%'));
    }
    $select->where($where->equalTo($this->table . '.status', '1'));
    $select->order('COALESCE(MAX(thread_replies.modified), threads.modified) DESC');

    $statement = $sql->prepareStatementForSqlObject($select);

//echo $select->getSqlString($this->adapter->getPlatform());
    $result = $statement->execute();
    $rows = new ResultSet();
    return $rows->initialize($result);
}

这是结果sql:

SELECT `threads`.*, `thread_replies`.`modified` AS `modifiedReply`, `b`.`building_name` AS `building_name`, `user`.`display_name` AS `display_name`, `user`.`username` AS `username` FROM `threads` 
LEFT JOIN `thread_replies` ON `thread_replies`.`thread_id` = `threads`.`thread_id` 
INNER JOIN `buildings` AS `b` ON `b`.`id` = `threads`.`building_id` INNER JOIN `user` ON `user`.`user_id` = `threads`.`created_by` 
WHERE `threads`.`status` = '1' 
ORDER BY `COALESCE``(``MAX``(``thread_replies`.`modified``)` ASC, `threads`.`modified``)` DESC

我得到以下错误：

Statement could not be executed (42S22 - 1054 - Unknown column 'COALESCE`(`MAX`(`thread_replies.modified`)' in 'order clause')

注意：这个问题与https://stackoverflow.com/a/20028142/4380588