If you have tried to use Pagination in CakePHP on a SQL Server database, you probably discovered that it does not work — and no, you are not stupid, there is a very real bug. Firstly, you will notice that each subsequent pagination screen merely displays the same records that were displayed on the first screen.
Fix part 1:
var $paginate = array(
‘order’ => array(
‘Model.id’ => ‘asc’
));
Put the above piece of code at the declaration section of you controller above your first method / action. If you run your code you will discover that it works partially now. If you have 34 records, and you limited them by 10, you will end up with4 pagination screens. Screen 1 (displays 1-10), screen 2 (displays 11-20), screen 3 (21-30), screen 4 (24-34). So essentially, screen 3 and 4 contain the exact same information. Very confusing, isn’t it. The bug seems to have been there since 2009 and I don’t think it will be solved any time soon.
Kevin Wentworth posted a patch (#5675) found on trac.cakephp.org
return ”SELECT * FROM (SELECT {$limit} * FROM (SELECT TOP {$offset} {$fields} FROM {$table} {$alias} {$joins} {$conditions} {$group}{$order}) AS Set1 {$rOrder}) AS Set2 {$order2}”;
It is suggested that the above line in dbo_mssql.php should be replaced with the following 2 lines:
$limitint = (int)$offset + 1 - (int)trim(str_replace(‘TOP’,”, $limit));
return ”SELECT * FROM (SELECT row_number() OVER ({$order}) as resultNum, {$fields} FROM {$table} {$alias} {$joins} {$conditions} {$group}) as numberResults WHERE resultNum BETWEEN {$limitint} and {$offset} ”;
However, the above patch of code may introduce a new bug. When you try to go to “Page 2″ of a pagination query and there is no ordering set for pagination, you will see the following error:
The ranking function "row_number" must have an ORDER BY clause.
Obviously the error is related to not having an order by clause, sadly the renderStatement does not seem to push the Primary Key or Index through so we cannot automatically assign an ORDER BY field, but at least we can prevent CakePHP from raising an error under this condition.
Fix part 2 + Workaround. Replace the first piece of code with a combination of the two below:
/* Hack to fix broken pagination */
if ($order > ”)
{
$limitint = (int)$offset + 1 – (int)trim(str_replace(‘TOP’,”, $limit));
return “SELECT * FROM (SELECT row_number() OVER ({$order}) as resultNum, {$fields} FROM {$table} {$alias} {$joins} {$conditi ons} {$group}) as numberResults WHERE resultNum BETWEEN {$limitint} and {$offset} “;
}
else
{
return “SELECT * FROM (SELECT {$limit} * FROM (SELECT TOP {$offset} {$fields} FROM {$table} {$alias} {$joins} {$conditions} {$group} {$order}) AS Set1 {$rOrder}) AS Set2 {$order2}”;
}
/* End of hack */
Once you have applied this patch, you should end up with pagination that works and doesn’t cause your system to crash when you have failed to provide an order by declaration.
Fix Part 3:
Finally, put the below part of code at the top of your controller.
var $paginate = array(
‘limit’ => 10,
‘order’ => array(
‘Model.id’ => ‘asc’));
Don’t forget that Model.id refers to your primary index / key.
Fix part 4:
If you would like to ensure that all models have the necessary ordering on Model.id, you can create component that sets the necessary order array for paginate:
1. Create a component called “system.php”
class SystemComponent extends Object
{
//called before Controller::beforeFilter()
function initialize(&$controller, $settings = array())
{
// saving the controller reference for later use
$this->controller = & $controller;
setPagination();
}
private function setPagination()
{
/* To work around sql server pagination bug */
if (isset ($this->controller->uses) )
{
if (is_array($this->controller->uses))
{
if (count($this->controller->uses) > 0)
{
$modelName = $this->controller->uses[0];
$this->controller->paginate = array(
‘order’ => array(
“$modelName.id” => ‘asc’
));
}
}
}
}
}
2. Add the component to either the AppController class if you would like the workaround application wide or add the component to the Controller you are working with: var $components = array(‘System’,'Scrum’);
If time permits, I’ll work on a better patch. Please let me know if this works for you or if it causes any problems.
