CODEWITHSUNDEEP
phpmysqlzend-frameworkzend-framework2
Faiyaz Alam
Faiyaz Alam

Reputation: 1217

how to fetch all post with comment order by latest post or comment(if any) - zend framework 2 php mysql

I want to show the list of all posts. If a new comment created for a particular post then this post will be come on the top of the list.

here is my function in model : 

   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);
}

here is the resulted 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

I am getting the following error : 

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

NOTE : this question is very similar to https://stackoverflow.com/a/20028142/4380588

Upvotes: 0

Views: 157

Answers (1)

Otto Sandström
Otto Sandström

Reputation: 745

You need to do wrap the order with an expression, this is because Zf2 tries to qoute all the identifiers in the order string. Change this

$select->order('COALESCE(MAX(thread_replies.modified), threads.modified) DESC');

to this

$select->order(new \Zend\Db\Sql\Expression('COALESCE(MAX(thread_replies.modified), threads.modified) DESC'));

Upvotes: 2

Related Questions