MainelyDesign.com Blog

Pagination with MSSQL in CakePHP

Posted on 05/07/2009 at 04:36 pm by Kevin Wentworth
Viewed 16,939 times | 0 comments

One thing, and there are a few, that I can't stand about having to use PHP and MSSQL Server together is the lack of support for pagination.  With PHP and MySQL- no problem use limit.  MSSQL- can't do it without all these ugly sub-queries.  I have to give it to the CakePHP developers though, for the built-in support for pagination in MSSQL.  Albeit it is flawed, it works.

The problem with the CakePHP support for pagination in MSSQL is that on the last page of results you get a full result set of whatever you've set as your records per page.  If you have 34 records and you are paginating 20 per page, you'll see 1-20 on the first page, but you'll see 14-34 on the second page.  This is due to a limitation of MSSQL prior to 2005.  Since we've recently upgraded to using MSSQL 2005 for the project I need to use MSSQL for, I decided to give this patch and try.  I recommend it to all MSSQL 2005 and up users.  It's almost as good as using MySQL.

It's pretty simple to add to your dbo_mssql.php file.  Yes, it hacks the core, but I think this functionality will be added in CakePHP 1.3 anyway.  All that's missing is a way to tell what version of MSSQL is being used and modify the pagination query appropriately.

In dbo_mssql.php (line 549):

Change to:

Thanks to celsowm for posting the patch (#5675).

Cheers,

-Kevin Wentworth