Re: Sorting pagination with HasMany and SQL Aggregation
Make life easier by adding an afterSave() callback to Rating to populate a field in Article with the average value (and possibly one for rating_count, but that can easily be done using counterCache). It's the more efficient way to do it and sorting would then be on the Article model. --~--~-~--~~~---~--~~ 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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Sorting pagination with HasMany and SQL Aggregation
in short,
Article hasMany Rating
the articles/index need to paginate and sorted with AVG(rating.score)
i found a good start in
http://groups.google.com/group/cake-php/browse_thread/thread/bece8aab31fbba72/1b27c78b0a3023d8?#1b27c78b0a3023d8
but archived, and last solution do not work at all. (cakephp 1.2.5)
my code:
class ArticlesController extends AppController {
// ...
var $paginate = array(
'Article' = array(
'limit' = 15,
'order' = 'avg_rating DESC',
'group' = 'Article.id'
)
);
// ...
function index() {
if (isset($this-passedArgs['sort'])) {
if ($this-passedArgs['sort'] == 'avg_rating') {
$this-paginate['Article']['order'] = 'avg_rating '.strtoupper
($this-passedArgs['direction']);
$this-paginate['Article']['direction'] = $this-passedArgs
['direction'];
unset($this-passedArgs['sort']);
}
}
$this-Article-bindModel(array(
'hasOne' = array(
'AvgRating' = array(
'className' = 'Rating',
'fields' = 'AVG(AvgRating.rating) AS avg_rating'
)
)
), false);
$this-set('articles', $this-paginate());
}
}
is there anything i can do for a more beautiful solution?
at least, i think, a little modification in paginator help can help
index controller during reset passedArg
--~--~-~--~~~---~--~~
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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Pagination with HasMany and SQL Aggregation
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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Pagination with HasMany and SQL Aggregation
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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
AW: Pagination with HasMany and SQL Aggregation
Hi, that sounds like pagination with conditions: http://groups.google.ch/group/cake-php/msg/09277228f45365ac If I understood you correctly: if you want an average rating you have to read first all your datasets and loop though the array and create an average value of the ratings. So paging won't help there. Anja -Ursprüngliche Nachricht- Von: cake-php@googlegroups.com [mailto:[EMAIL PROTECTED] Im Auftrag von mwcbrent Gesendet: Dienstag, 4. November 2008 05:21 An: CakePHP Betreff: Pagination with HasMany and SQL Aggregation 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
