4 Mar
Custom CakePHP 1.2 Pagination Queries
(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:
-
/**
-
* Custom paginate method
-
*/
-
function paginate($conditions, $fields, $order, $limit, $page = 1, $recursive = null) {
-
$conditions[] ="1 = 1 GROUP BY week, away_team_id, home_team_id";
-
$recursive = -1;
-
-
return $this->findAll($conditions, $fields, $order, $limit, $page, $recursive);
-
}
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:
-
/**
-
* Custom paginateCount method
-
*/
-
function paginateCount($conditions = null, $recursive = 0) {
-
$sql = "SELECT DISTINCT ON(week, home_team_id, away_team_id) week, home_team_id, away_team_id FROM games";
-
$this->recursive = $recursive;
-
$results = $this->query($sql);
-
-
}
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 >> CakePHP || pagination
Posted by Walker Hamilton on 04.03.08 at 10:29 pm
awesome! thanks!
One question with little relevancy....why the -1 in recursiveness?
Posted by Chris Hartjes on 04.03.08 at 10:29 pm
@Walker
I did that to make sure I only get back the exact data I want and none of the associative stuff. Maybe setting it to 0 would be fine too. Seems to work okay with -1, but that might be overkill.
Posted by dericknwq on 04.03.08 at 10:29 pm
Based on my current understanding, it is not possible to have multiple paginate method for each model right? The thing is sometimes we might need to have different fields or different logic of returning the data from the same model and with the current Controller::paginate(), I think it isn't possible to pass in custom params so that in ur Model's paginate() you could refer to that param and decide which logic to use.
Of cos it would be right to just pass in the conditions and fields and everything else from the controller, but we want to maintain our fat models right? The only possible solution I could see is to just overwrite paginate() in AppController to do that but perhaps there might be something that I've missed?
Posted by noddy on 04.03.08 at 10:29 pm
Great tutorial! Thanks!
I have the custom paginate function working fine. However when I try and add the page x out of xx pages I'm getting the following error in the view:
"Call to a member function counter() on a non-object"
My view files contains the $paginator->counter , prev, next and numbers . What am I missing here?
Posted by leveille on 04.03.08 at 10:29 pm
I'm sure you noticed:
http://devzone.zend.com/article/3240-Chris-Hartjes-Custom-CakePHP-1.2-Pagination-Queries
Posted by Radek on 04.03.08 at 10:29 pm
Hello, you don't have to redefine paginate* methods, you can simply change your paginate var befor custom queries. This is my example:
function user_index() {
$this->paginate = array(
'limit' => 20,
'conditions' => array(
'Tag.user_id' => $this->Auth->user('id')
)
);
$this->Tag->recursive = 0;
$this->set('tags', $this->paginate());
$this->render('index');
}
Posted by Chris Hartjes on 04.03.08 at 10:29 pm
@Radek
Did you even read the blog post? There *are* times when you need to override the paginateFind() and paginateCount() methods. Tell me, how would you do my example query in the blog post without overriding paginateFind and paginateCount?
Posted by rick on 04.03.08 at 10:29 pm
But what if you want to use paginate with different custom queries in several actions inside your controller? how do you do that?
Posted by Adam on 04.03.08 at 10:29 pm
I'm asking myself the same question. What if I have different actions that require different pagination configurations. I have one that can use the default pagination method, but another one that needs a custom query. How would I go about addressing that?
Posted by Brendon Kozlowski on 04.03.08 at 10:29 pm
Thanks for this, Chris. Just letting you know that even now this solution is still proving useful in solving problems. Much thanks for sharing!
Posted by Matthew Rich on 04.03.08 at 10:29 pm
Thanks for this Chris -- this was stunningly useful for me in implementing pagination for a model based on a web service query.
Posted by richard on 04.03.08 at 10:29 pm
thanks to Radek his suggestion works. i really dont know why custom pagination doesnt work.
Posted by Personal » Blog Archive » PHP kelabu on 04.03.08 at 10:29 pm
[...] tapi ternyata agak kurang suitable dengan fungsi pagination-nya CakePHP, but at last, I found the solution), gue dihadapkan lagi dengan masalah PDF document [...]
Posted by Joel on 04.03.08 at 10:29 pm
I have been trying to adapt this method.
So far, only using the first default option works.
if I change the findAll method (from the pagination) to writing a custom query.
There is results return but the paginate (controller) method is broken. All the $limit $page parameter are not send to the controller at all. ..
So it's kinda like back to square one ....
Joel
Posted by Chris Hartjes on 04.03.08 at 10:29 pm
@Joel
I'm not sure I fully understand the problem you're running into here. However, if you do switch from using findAll with new conditions to using a custom query, the "paginate" method in your model MUST return results in the same format as a findAll(...) command would or else it will not work.
Hope that helps.