Sort cell breakdown by computed field (COUNT)

Question

I had a problem sorting a paginated list when using a computed field like COUNT () in cakephp 1.3

Let's say that I have two models: an article and comments (1 article x N comments), and I want to display a broken list of articles, including the number of comments for each of them. I would 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 rewrite the findCount() method to findCount() using the group)

The problem is that in the view, the sort() method will not work:

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

I managed to create a workaround by “tricking” pagination and sorting:

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 there is too much code for something that should be transparent to developers. (I feel like making a cake) So I ask if there is an even easier way. Maybe the cake has this function, but decided to hide it. O_O .. there is nothing about this in the documentation, and I have not found another good solution for SO .. how do you do this?

Thanks in advance!

Answer 1

maybe your problem can be solved using virtual fields ?

If you create a custom field for the computed field, you can sort it using the Paginator-> sort () method in this custom field.

I found that the solution is there in the comments (there is no need to configure the paginate method, etc. using custom fields, and not in the adnan initial solution).

Answer 2

What you can do is use the Cake counterCache function in the model definition. Instead of calculating the number of comments while reading, you add nbr_comments as the int field in the articles table. Each time a Comment inserted or deleted, nbr_comments will be updated automatically.

In your Comment model:

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

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