How To Do Custom Pagination In CakePHP

Sometimes we need to do pagination in CakePHP using a traditional SQL query, thereby the CakePHP predefined pagination functionality is lost.

We can implement pagination method in the Model or Behavior when we are using standard SQL query in CakePHP. Just need to make sure that the result cannot be obtained with core model methods, or a custom finder before implementing custom queries pagination.

But we can’t just use the standard pagination on the custom queries, we have to override the paginate functions in model or behavior.

To use our own method/logic to override the CakePHP pagination in the model, we need to add two functions: paginate() and paginateCount().

In these functions, we can implement our own paginate logic.

Overriding paginate() function in CakePHP Post Model:

public function paginate($conditions, $fields, $order, $limit, $page = 1, $recursive = null, $extra = array()) {
 $orderStr = '';
 foreach($order as $k => $ord) {
 $orderStr[] = $k . ' ' . $ord;
 }
 $orderStr = 'ORDER BY '. implode(', ', $orderStr);

 $qryCond = '1';
 if (isset($conditions['Post.title LIKE'])) {
 $qryCond = 'title LIKE \''.$conditions['Post.title LIKE'].'\'';
 }

 $qryFlds = '*';
 if ($fields) {
 $qryFlds = implode(', ', $fields);
 }

 $sql = 'SELECT '.$qryFlds.' FROM posts as Post WHERE '.$qryCond.' '.$orderStr . ' LIMIT ' . (($page-1) * $limit) . ', ' . $limit;
 $results = $this->query($sql);
 return $results;
}

Overriding paginateCount() function in CakePHP Post Model:

public function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
 $qryCond = '1';
 if (isset($conditions['Post.title LIKE'])) {
 $qryCond = 'title LIKE \''.$conditions['Post.title LIKE'].'\'';
 }

 $sql = 'SELECT COUNT(*) as count FROM posts as Post WHERE '.$qryCond;

 $this->recursive = -1;

 $results = $this->query($sql);
 return $results[0][0]['count'];
}

Using in the Post Controller: We can adjust the fields to get, column ordering, add certain conditions or adjust row limit per page.

public function index() {
 $this->paginate = array(
 'limit' => 10,
 'fields' => array('Post.id', 'Post.title', 'Post.created'),
 'conditions' => array('Post.title LIKE' => '%search_keyword%'),
 'order' => array('Post.title' => 'asc', 'Post.id' => 'asc')
 );
 try {
 $this->set('posts', $this->paginate('Post'));
 } catch (NotFoundException $e) {
 $this->redirect('index_custom');
 }
}

The above example is based on specific model and requirement, need to adjust the methods according to the requirements.

See Also: Creating a custom handler session in CakePHP 2.x

Best of luck and happy pagination.

Hope you liked this. Let me know if you want to add anything?