Cakephp Custom group by pagination and a calculated field

Cakephp Custom group by pagination and a calculated field

Example of how to use the CakePHP paginator helper with the group by condition. this is from the original article from http://wiltonsoftware.com/posts/view/custom-group-by-pagination-and-a-calculated-field




In the Controller

<?
var $helpers = array('Paginator');
var $paginator = array('limit' => 20);

function admin_index($filter=null) {
 $conditions = array();
 $this->Comment->recursive = 0;
 if ($filter == 'count') {
     $conditions = array('Comment.status = 0');
     $this->paginate['Comment'] = array(
         'fields' => array(
             'Comment.id', 'Comment.ip', 'Count(Comment.ip) as Count'
         ),
         'conditions' => array(
             'Comment.status = 0'
         ),
         'group' => array(
             'Comment.ip'
         ),
         'order' => array(
             'Count' => 'desc'
         )
     );
     $data = $this->paginate('Comment', $conditions);
  
 } else {
     if ($filter == 'spam') {
         $conditions = array('Comment.status = 0');
     } else {
         $conditions = array('Comment.status > 0');
     }
     $this->paginate['Comment'] = array(
         'order' => array(
             'Comment.id' => 'desc'
         )
     );
 }
 $data = $this->paginate('Comment', $conditions);
}
?>

Model

function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
  $parameters = compact('conditions');
  $this->recursive = $recursive;
  $count = $this->find('count', array_merge($parameters, $extra));
  if (isset($extra['group'])) {
      $count = $this->getAffectedRows();
  }
  return $count;
}

View

$paginator->options(array('url' => $this->passedArgs));