I wanted to use the CakePhp pagination helper to paginate some complex records. The model that the paginator was referencing had both
belongsTo associations are not a problem. hasMany, I discovered, are — which makes sense when I think about.
As the ticket notes:
You can only paginate LEFT JOIN tables by default. hasMany relationships are performed as a separate query, and thus cannot have conditions in the primary paginate() call. If you wish to do this, you need to implement paginate() and paginateCount() in your model.
So that’s what I did. Only it wasn’t quite as simple as I thought it would be.
The CakePhp manual does provide some guidance: 4.9.4 Custom Query Pagination. This comes from the example originally posted here (littlehart.net), where there are additional comments to both confuse and clarify a bit.
One thing that I didn’t see ever made explicit is how exactly you integrate the custom model methods you create, paginate and paginateCount, with the paginator syntax in your controller or helper. Well, it turns out, as you would probably hope, that it’s fairly seamless. You just use the standard pagination markup , and the custom methods override Cake’s default methods (this changeset illustrates).
With hasMany associations, as is the case in the example above from Cake’s website, you are probably going to need to use
GROUP BY syntax. For my particular case, this required a custom SQL query, which meant using Cake’s Model class’s
query. (I’m a little surprised and concerned that it doesn’t support bind parameters.)
I’ve posted the relevant code to my wiki: http://www.klenwell.com/is/Paste20090117
I hope it helps steers someone to the answer they’re looking for.