(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;
-
-
'conditions' => $conditions,
-
'recursive' => $recursive,
-
'fields' => $fields,
-
'order' => $order,
-
'limit' => $limit,
-
'page' => $page
-
);
-
-
return $this->find('all', $params);
-
}
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.
Tags: CakePHP, pagination
awesome! thanks!
One question with little relevancy....why the -1 in recursiveness?
@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.
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?
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?
I'm sure you noticed:
http://devzone.zend.com/article/3240-Chris-Hartjes-Custom-CakePHP-1.2-Pagination-Queries
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');
}
@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?
But what if you want to use paginate with different custom queries in several actions inside your controller? how do you do that?
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?
Thanks for this, Chris. Just letting you know that even now this solution is still proving useful in solving problems. Much thanks for sharing!
Thanks for this Chris -- this was stunningly useful for me in implementing pagination for a model based on a web service query.
thanks to Radek his suggestion works. i really dont know why custom pagination doesnt work.
[...] tapi ternyata agak kurang suitable dengan fungsi pagination-nya CakePHP, but at last, I found the solution), gue dihadapkan lagi dengan masalah PDF document [...]
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
@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.
Hi, thanks for the snippets.
Is it possibile to use pagination with a full ->query("select ... group by ... order by ... limit ...") instead? How do I setup the paginator in a situation like this? I'd really appreciate any hint about this.
Thanks.
@Rob
You can put any query you want into the paginate method above, so long as it returns results in the same format that find('all') produces (findAll is deprecated, and I will update the post to reflect that).
Thanks for that code Radek, I was trying to figure out how to change the paginate var in a controller function (I have need for different pagination records per page in one controller). I find it funny that a lot of this info is not readily available in the Cakephp docs
i had the same trouble Adam pointed out.
after hours of trying to get a special pagination to work without losing my normal pagination in this model, i came up with the following:
in your model.php, create a new class ModelExt extends Model {}
and inside this class insert the above 2 functions
now you can init. it and use this custom query pagination in your controller by passing this object into the pagination method:
$this->ModelExt = new ModelExt();
$res = $this->paginate($this->ModelExt);
as i said - hours of trying out every possible solution...
Hi,
I have to have one page that has 3 tables on it. Each of the tables needs to be paginated, and they are ALL from the SAME model. They are for Trades, so I'm having 3 tables, one will show all trades in state open, the other for all trades in state closed, and the final all in pending state.
My question is, how can I call the paginate 3 different times in the same model? I do:
$this->set('trades1', $this->paginate('Trade', array('Trade.trading_state_id'=>'open')));
$this->set('trades2', $this->paginate('Trade', array('Trade.trading_state_id'=>'closed')));
$this->set('trades3', $this->paginate('Trade', array('Trade.trading_state_id'=>'pending')));
Then when I render the screen, I use the appropriate data for the appropriate table. HOWEVER, all of the paging stats (next, previous, # of records) are all the data from the last paginate call of course...
I'm using AJAX to render only the appropriate section, although I don't know where the user is coming from, it is re-rendering the entire screen (all 3 tables) in the desired output div instead of just the one.
In summary, how can you call paginate multiple times for the same model in your controller? Also, is there a way of knowing where you came from (ie which table did you click next on) from your views?
Hope this makes sense. Any help would be appreciated.
@Tara
I don't know of any EASY way to accomplish what you're asking, as I've only played around with one set of pagination links per page.
Any 'not so easy' ways that you can think of
I have tried a few out and not having a lot of luck yet....
@Tara
I've never tried having more than one set of pagination links on a page so I don't know how to approach this. Maybe something like requestAction pulling in info from three different views?
Please fix typo:
Change this:
'fields' = $fields,
To this:
'fields' => $fields,
@Tim
Fixed the typo. Thanks for pointing it out.
You're welcome - thanks for the tutorial!!!
[...] [upmod] [downmod] @TheKeyboard » Blog Archive » Custom CakePHP 1.2 Pagination Queries (www.littlehart.net) 2 points posted 8 months, 3 weeks ago by jainaks2008 tags sql cake [...]