How To Do Custom Pagination In CakePHP

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.

Contents

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?

Summary
How To Do Custom Pagination In CakePHP
Article Name
How To Do Custom Pagination In CakePHP
Description
We can implement pagination method in the Model or Behavior when we are using standard SQL query in CakePHP.
Author
Publisher Name
Andolasoft
Publisher Logo

Your recently viewed posts:

    Contact Us

    We’d love to help & work with you




    When do you want to start ?


    Enter your email address to stay up to date with the latest news.
    Holler Box

    Exit pop up

    Sad to see you leaving early...

    From "Aha" to "Oh shit" we are sharing everything on our journey.
    Enter your email address to stay up to date with the latest news.
    Holler Box