[FIXED] Cakephp pagination sort by calculated field ( COUNT )

Issue

I had a problem sorting a paginated list when using a calculated field such as COUNT() in cakephp 1.3

Let's say that i have two models: Article and Comments (1 article x N comments) and i want to display a paginated list of the articles including the number of comments for each one. I'd have something like this:

Controller:

$this->paginate = array('limit'=>80,
                        'recursive'=>-1,
                        'fields'=>array("Article.*","COUNT(Comment.id) as nbr_comments"),
                        'joins'=>array(array(   'table' => 'comments',
                                                    'alias' => 'Comment',
                                                    'type' => 'LEFT',
                                                    'conditions' => array('Comment.article_id = Article.id'))
                                            ),
                        'group'=>"Article.id"
                        );

(i had to overwrite the findCount() method in order to paginate using group by)

The problem is that in the view, the sort() method won't work:

<th><?php echo $this->Paginator->sort('nbr_comments');?></th> //life is not that easy

I was able to create a workaround by "cheating" the pagination and sort:

Controller

$order = "Article.title";
$direction = "asc";
if(isset($this->passedArgs['sort']) && $this->passedArgs['sort']=="nbr_comments")
    $order = $this->passedArgs['sort'];
    $direction = $this->passedArgs['direction'];
    unset($this->passedArgs['sort']);
    unset($this->passedArgs['direction']);
}
$this->paginate = array(... 'order'=>$order." ".$direction, ...);
$this->set('articles', $this->paginate());
if($order == "clicks"){
    $this->passedArgs['sort'] = $order;
    $this->passedArgs['direction'] = $direction;
}

View

<?php $direction = (isset($this->passedArgs['direction']) && isset($this->passedArgs['sort']) && $this->passedArgs['sort'] == "nbr_comments" && $this->passedArgs['direction'] == "desc")?"asc":"desc";?>
<th><?php echo $this->Paginator->sort('Hits','clicks',array('direction'=>$direction));?></th>

And it works.. but it seems that is too much code for something that should be transparent to the developper. (It feels like i'm doing cake's work) So i'm asking if there's another simpler way. Maybe cake has this functionallity but decided to hide it.. o_O.. there's nothing about this on the documentation, and i haven't found another good solution on S.O... how do you do it?

Thanks in advance!

Solution

maybe your problem can be solved using Virtual fields ?

If you create a custom field for your computed field, you will be able to sort using Paginator->sort() method on that custom field.

I found that solution there in the comments (there is no need to customize the paginate method etc. using custom fields instead of in adnan's initial solution).

Answered By - Philipili