paginate and custom find failing count
Reported by franiglesias | December 30th, 2010 @ 02:03 AM | in Future
Trying to use controller->paginate() with some custom model find methods that use joins, I've got the following error: Warning (512): SQL Error: 1054: Unknown column 'Channel.slug' in 'where clause'.
I guess that the error comes from the model->_findCount method called by controller->paginate(). This method should honor the custom find, but it doesn't and constructs the query using only the data in the controller->paginate property for the model being paginated.
So, I suggest to change the model->_findCount method to build the count query starting form the generated by the find method passed in the options. A possible patch is attached.
Comments and changes to this ticket
-
franiglesias January 9th, 2010 @ 05:52 AM
- → Tag changed from “model, _findcount” to “defect, model”
-
Mark Story January 10th, 2010 @ 11:25 AM
- → Milestone set to “1.2.5”
How does your custom find know the difference between the count and the query? Also you can define
paginateCountin your model and handle special cases there. Seems to me that this feature can be easily handled with no core changes. -
franiglesias January 10th, 2010 @ 12:23 PM
Thanks, Mark. Firstly I must say that I'm using the _findCustom($state, $query, $results) syntax to write my custom find methods.
Studying the code of Controller->paginate() I've learned that it calls model->find('count') to compute the total number of records and then calls model->find(type) to retrieve the data paginated.
I've also learned that Controller->paginate() pass info about the find type to the model->find('count') method, but nothing is done with this information.
The method that performs the count query is model->_findCount($state, $query, $results), and you can check if a custom method is going to be used to retrieve the data by looking at the 'type' key of the $query parameter in the 'before' phase of the method.
The actual code does nothing with this information, so the count query is build with the options set in the Controller->paginate class variable. So, if you are paginating with a custom find method, all changes to the query in the custom find are ignored.
So, my approach is to check if the _findCount is called from Controller->paginate and check if a custom find method needs to be performed. If yes, then call this method to get the full query and build the count query from there.
if (isset($query['type']) && $query['type'] != 'count') { $query = $this->{'_find' . ucfirst($query['type'])}('before', $query); }(You could need to check that the 'type' value is not a default find type)
With respect to paginateCount, I think this approach is better beacause you don't need to treat your custom find as special cases.
-
Mark Story January 14th, 2010 @ 11:14 PM
- → State changed from “new” to “works-for-me”
Since the existing solution is a good one, I'll close this ticket, if I've misread things, or the issue is still valid, leave a comment and we can go from there :)
-
franiglesias January 15th, 2010 @ 11:09 AM
I'm afraid I have not explained it well enough (I'm not very fluent in English).
I have tried to write a test case to expose the problem. I used the core Controller Test as starting point. Following files are attached.
paginate.test.php shows that the total count after controller->paginate doesn't take into account query modifications in the custom find method, so it fails.
paginate_fix.test.php show my _findCount modification and test pass.
add_to_controller.test.php is a test you can add to the core controller.test.php to expose the problem
-
Mark Story January 15th, 2010 @ 05:10 PM
- → Milestone changed from “1.2.5” to “1.3.0”
- → Tag changed from “defect, model” to “enhancement, model”
- → State changed from “works-for-me” to “open”
Couldn't this be implemented in an AppModel like:
function _findCount($state, $query, $results = array()) { if ($state == 'before') { if (isset($query['type']) && $query['type'] != 'count') { $query = $this->{'_find' . ucfirst($query['type'])}($state, $query); } return parent::_findCount($state, $query); } return parent::_findCount($state, $query, $results); }My only concern with implementing it in the core
_findCount, is there is a possibility that more than just $query being modified is being done on the before state. So this could affect other uses. Re-opening as an enhancement for consideration. -
franiglesias January 15th, 2010 @ 05:23 PM
I see your concern. In fact, I began to use this way.
Thanks for the patience.
-
-
-
Billy Bradley December 12th, 2011 @ 03:08 PM
First off, Thank You for the count fix : )
I have two different apps i'm working on, each has a custom find for pagination.
While the count works great on both, there is a problem with one.
the next previous paging links stop acting right.With the conditions i am using there should be 11 results on a page with a paginator limit of 9.
when i see the sql queries the count is working correctly, applying the same conditions as my find query with the expected results.
but the links don't show.
when i debug $this->Paginator->params i see that there is a page count of only 1 when there should be 2.Where does paging get this number from?
-
-
-
-
-
Please Sign in or create a free account to add a new ticket.
With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.
Create new ticket
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »
Source available from github
Repository is at http://github.com/cakephp/cakephp
Creating a bug report
When creating a bug report, please include as much relevant information as possible. Please include code to reproduce the issue. Or even better, make a unit test. Either change an existing test or add a new test to show that the expected behavior is not occuring.
Rachman Chavik