Cakephp pagination sort by calculated field ( COUNT )

Question

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!

Answer 1

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).

Answer 2

What you may want to do is use Cake's counterCache functionality in your model definition. Rather than calculating the comment counts at read time, you add nbr_comments as an int field in your articles table. Every time a Comment is inserted or deleted, nbr_comments will be updated automatically.

In your Comment model:

var $belongsTo = array(
    'Article' => array(
        'counterCache' => 'nbr_comments'
    )
);

Now you can just use $this->Paginator->sort('Article.nbr_comments'); You wont need to do anything funky in your controller.