Converting MySql query to Cakephp 2.6

Question

Hello friends I am new to Cakephp 2.6, I need to convert MySql query to Cakephp 2.6 . I am getting the desired output, I am fetching the last conversation between client and consultant group by task ID. I don't want to write the Model:query() as I need default pagination of cakephp too.

This is my following table, please have a look

SELECT * FROM (SELECT task_id, MAX(created) AS created FROM task_conversations GROUP BY task_id) AS x 
JOIN task_conversations USING (task_id, created) 
WHERE (client_id=3 OR consultant_id= 3) ORDER BY modified ASC

This is the following output of the above query:

I am trying to resolve it by writing the following lines of code

$this->paginate = array(
            'conditions' => array(
                'TaskConversation.sender_id'    => $consultant['User']['id'],
                'Task.status'                       => 1
            ),
            'group'         => 'TaskConversation.task_id',
            'limit'         => 3,
            'order'         => array('TaskConversation.conversation'=> 'ASC'
            ),
            'recursive'     => 2
        );
        $this->set('tasks', $this->Paginator->paginate());

I will require the pagination too here. Please let me know your feedback. Thanks in advance

Answer 1

Finally I got the solution for it. Here is the solution:

I have added custom pagination functions in Model. Here is the code:

public function paginate($conditions, $fields, $order, $limit, $page = 1, $recursive = null, $extra = []) {    


     if(isset($extra['isCustomPagination']) && $extra['isCustomPagination']==0) {
                $recursive = -1;

    return $this->find(
        'all',
        compact('conditions', 'fields', 'order', 'limit', 'page', 'recursive')
    );
            }else{
                $recursive = -1;
        // Mandatory to have
        $this->useTable = false;
        $sql = '';
        $sql .= "(SELECT * FROM (SELECT task_id, MAX(created) AS created FROM task_conversations GROUP BY task_id) AS Task JOIN task_conversations as TaskConversation USING (task_id, created) INNER JOIN tasks as Task2 ON Task2.id=Task.task_id INNER JOIN users as Client ON TaskConversation.client_id=Client.id INNER JOIN users AS Consultant ON TaskConversation.consultant_id = Consultant.id where (TaskConversation.consultant_id = ".$extra['extra']['consultant_id'].") order by TaskConversation.modified ASC) ";

        // Adding LIMIT Clause
        $sql .= ' LIMIT '.(($page - 1) * $limit) . ', ' . $limit;
        $results = $this->query($sql);
        return $results;
            }
    }

    public function paginateCount($conditions = null, $recursive = 0, $extra = array()) {

      if(isset($extra['isCustomPagination']) && $extra['isCustomPagination']==0) {

                    $parameters = compact('conditions');
      if ($recursive != $this->recursive) {
    $parameters['recursive'] = $recursive;
   }
   $count = $this->find('count', array_merge($parameters, $extra));
   return $count;
             }
             else {
                 $sql = '';
        $sql .= "(SELECT * FROM (SELECT task_id, MAX(created) AS created FROM task_conversations GROUP BY task_id) AS Task JOIN task_conversations as TaskConversation USING (task_id, created) INNER JOIN tasks as Task2 ON Task2.id=Task.task_id INNER JOIN users as Client ON TaskConversation.client_id=Client.id INNER JOIN users AS Consultant ON TaskConversation.consultant_id = Consultant.id where (TaskConversation.consultant_id = ".$extra['extra']['consultant_id'].") order by TaskConversation.modified ASC) ";
        $this->recursive = $recursive;
        $results = $this->query($sql);
        return count($results);
             }
    }

In the controller I have added the following lines:

$this->paginate = array('TaskConversation'=>array('limit'=>2,'extra'=>array('client_id'=>$consultant['User']['id'],'consultant_id'=>$consultant['User']['id'])));

If you don't want to use custom pagination in other function related with same model then I have passed this in paginate 'isCustomPagination' => 0.

$this->TaskConversation->contain('Task','Client','Consultant');
         $this->paginate = array(
            'conditions' => array(
                'TaskConversation.task_id'  => $id,
                'Task.status'               => 1
            ),
            'order'         => array('TaskConversation.created'=> 'ASC'
            ),
            'recursive'     => 2,
            'isCustomPagination' => 0,
         );
        $this->set('conversations', $this->Paginator->paginate());