4 Mar

Custom CakePHP 1.2 Pagination Queries

Posted by Chris Hartjes in CakePHP.

(Note: I will be cross-posting a version of this to the CakePHP Cookbook within a day or two).

As amazing as the built-in CakePHP pagination helper is, sometimes you have to create your own custom queries for data you wish to paginate. For the simulation baseball league site I wanted to be create an admin area where I could paginate through the series instead of individual games. It took some digging around with google, but I found some info in a thread (thanks to Baz for contributing that tidbit to the thread) on how to do just that.

Okay, so it turns out that the paginate() method that is used to generate the data that you (oddly enough) paginate through takes the same arguments as Model::findAll(). So, if you want to use your own query, you simply create a 'paginate' method for your model. In my case, I needed a query that would group things together (because a 'series' is simply a collection of all games between two teams in a particular week). The query stuff I did is ugly because there currently is no support 'group by' in CakePHP (although that would be a very interesting project to tackle). So, here's what I did:

PLAIN TEXT
PHP:
  1. /**
  2. * Custom paginate method
  3. */
  4. function paginate($conditions, $fields, $order, $limit, $page = 1, $recursive = null) {
  5.     $conditions[] ="1 = 1 GROUP BY week, away_team_id, home_team_id";
  6.     $recursive = -1;
  7.     $fields = array('week', 'away_team_id', 'home_team_id');
  8.  
  9.      return $this->findAll($conditions, $fields, $order, $limit, $page, $recursive);
  10. }

So, that's the first part of the custom query stuff taken care off. Next, I needed to be able to properly count the number of 'series' so that the numbers (you know, that stuff that says 'page 7 of 23') come out properly. Again, you can override the paginateCount() method if you need to. It uses the same parameters as Model::findCount(). Now, the custom query I'm using below is Postgres-specific, so YMMV:

PLAIN TEXT
PHP:
  1. /**
  2. * Custom paginateCount method
  3. */
  4. function paginateCount($conditions = null, $recursive = 0) {
  5.     $sql = "SELECT DISTINCT ON(week, home_team_id, away_team_id) week, home_team_id, away_team_id FROM games";
  6.     $this->recursive = $recursive;
  7.     $results = $this->query($sql);
  8.  
  9.     return count($results);
  10. }

So there you have it. Hope this helps out anyone who's been trying to figure out how to use custom queries with their pagination.

Article Tags >> ||

11 Dec

CakePHP Pagination With A HABTM Relationship

Posted by Chris Hartjes in CakePHP.

There are usually lots of Has And Belongs To Many relationship questions on the CakePHP mailing list. Since I am stupid about this stuff, I sought out Nate Abele and bugged him via IM until he agreed to give me an example of how to do this. Thanks Nate!

PLAIN TEXT
PHP:
  1. class Tag extends AppModel {
  2.  
  3.     var $hasAndBelongsToMany = 'Post';
  4.    
  5.     function paginate($conditions = null, $fields = null, $order = null, $limit = null, $page = 1, $recursive = null) {
  6.         $tag = $conditions['tag'];
  7.         unset($conditions['tag']);
  8.  
  9.         $this->hasAndBelongsToMany['Post'] = am(
  10.             $this->hasAndBelongsToMany['Post'],
  11.             compact('conditions', 'fields', 'order', 'limit', 'page')
  12.         );
  13.         return $this->findByName($tag);
  14.     }
  15.  
  16.     function paginateCount($conditions = null) {
  17.         $tag = $conditions['tag'];
  18.         unset($conditions['tag']);
  19.         $tmp = $this->hasAndBelongsToMany['Post'];
  20.         $this->hasAndBelongsToMany['Post']['fields'] = array('id');
  21.         $tag = $this->findByName($tag);
  22.         $this->hasAndBelongsToMany['Post'] = $tmp;
  23.         return count($tag['Post']);
  24.     }
  25. }
  26.  
  27. ?>
  28.  
  29. <?
  30. // Controller code
  31.  
  32. $data = $this->paginate('Tag', array('tag' => $tag));
  33. // Where $tag = some tag name

Okay, that's great but WHY does it work? Normally, when you do pagination you have to pass it a bunch of different parameters so it knows what records you wish to include as part of the data set you want to paginate over. In this example, you've already established that Tag HABTM Post. In order for the pagination query to correctly pull out the Posts that are also associated with the Tag, you need to merge the parameters you passed in to your 'paginate' method so that the CakePHP data mapping functionality knows what associated records are to be included. Then run your $this->findByName(...) and you're all set.

Article Tags >> || ||
Want to advertise on this blog? Send email to chartjes@littlehart.net
GTcars Canadian Car Audio TurboDodge Car For Sale Sign
Audi Forum Mustang Forum Dodge Intrepid Miata Turbo
GTscene Pontiac Bonneville


@TheKeyboard is Digg proof thanks to caching by WP Super Cache!