Quantcast

Pagination with HasMany and SQL Aggregation

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Pagination with HasMany and SQL Aggregation

mwcbrent

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 [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

AW: Pagination with HasMany and SQL Aggregation

acl68

 
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: [hidden email] [mailto:[hidden email]] 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 [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Pagination with HasMany and SQL Aggregation

David C. Zentgraf
In reply to this post by mwcbrent

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 [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Pagination with HasMany and SQL Aggregation

grigri

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" <[hidden email]> 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 [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---

Loading...