Re: Pagination with HasMany and SQL Aggregation

Of course it's possible - this is cake after all...

class ArticlesController extends AppController {

// ...
var $paginate = array(
'Article' => array(
'limit' => 15,
'order' => 'avg_rating DESC',
'group' => 'Article.id'
)
);

// ...

function index() {
$this->Article->bindModel(array(
'hasOne' => array(
'AvgRating' => array(
'className' => 'Rating',
'fields' => 'AVG(AvgRating.rating) AS avg_rating'
)
)
), false);
$this->set('articles', $this->paginate());
}

}

If you want pagination links to work with aggregate fields like
avg_rating, I haven't found an "easy" way to do this yet, however this
does work:

// controller:
function index() {
if (isset($this->passedArgs['sort'])) {
if ($this->passedArgs['sort'] == 'avg_rating') {
$this->paginate['Article']['order'] = 'avg_rating ' .
(empty($this->passedArgs['direction']) ? 'ASC' : strtoupper($this-
>passedArgs['direction']));
unset($this->passedArgs['sort']);
unset($this->passedArgs['direction']);
}
}
// continue as normal
}

// view:
<?php echo $paginator->sort('avg_rating');?>

hth
grigri

On Nov 4, 7:33 am, "David C. Zentgraf" <dec...@gmail.com> wrote:
> I needed to that a little while ago and got it to work with some  
> slightly ugly hacking that probably doesn't scale too well, but it  
> might work for you too. :)
>
> This belongs in the controller:http://bin.cakephp.org/view/1990384829
>
> On 4 Nov 2008, at 13:20, mwcbrent wrote:
>
>
>
> > I'm not sure that this is possible but if it is, it would put Cake
> > into a special place in my heart.  I have 2 related models Articles
> > and Ratings.
>
> > Articles hasMany Ratings
>
> > A Rating has a User ID an Article ID and a Rating #.  In order to get
> > an articles rating I need to query all ratings by the Article ID and
> > average them out.
>
> > How would I paginate all Articles ordered by Rating?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to cake-php+unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---