Pagination with MSSQL in CakePHP

Posted on 05/07/2009 at 04:36 pm by Kevin Wentworth
Viewed 12,072 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):

  1. return "SELECT * FROM (SELECT {$limit} * FROM (SELECT TOP {$offset} {$fields} FROM {$table} {$alias} {$joins} {$conditions} {$group} {$order}) AS Set1 {$rOrder}) AS Set2 {$order2}";

Change to:

  1. $limitint = (int)$offset + 1 - (int)trim(str_replace('TOP','', $limit));
  2. 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} ";

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


-Kevin Wentworth

Bookmark and Share

Tags for Pagination with MSSQL in CakePHP

Cakephp | Database | Web Programming | Mssql

Comments for this Posting

No comments. Be the first to post a reply.

Sorry, comments are closed for this posting.

Please Email Kevin if you have any questions. Thanks!

Meet Site Avenger - Hosted Content Management System

Powered By: Site Avenger | Site Production: Saco Design