Cake’s Pagination component gives us – almost – everything we need to paginate some Model data: query management, sort functions, number links, etc, everything perfectly tied together.
One thing that I needed and it did miss was the possibility to sort by a custom field (generated via SQL SELECT … AS …). It only allows, at first glance, to sort by table columns and virtual fields.
Take this code as a very basic example:
$this->paginate = array( 'fields' => array('User.name', 'COUNT(Post.id) as posts_count'), 'joins' => array(array('table' => 'posts', 'alias' => 'Post', 'type' => 'LEFT', 'conditions' => array('User.id = Post.user_id'))) ); $users = $this->paginate('User');
If you wanted to sort by posts_count you have to do a little hack: set posts_count as a virtual field, pointing to itself. Put the following code before the call to paginate():
$this->User->virtualFields = array( 'posts_count' => 'posts_count', );