Recent Posts
- (09/10) Fixing Warning: the ECDSA host key for 'github.com' differs from the key for the IP addressTAGS:Web Server Admin
- (12/26) CakePHP 3 - Getting List of Column Definitions from a Table (like schema())TAGS:CakephpCake3
- (09/14) Change Order of Loaded Behaviors in CakePHP 3TAGS:Cake3CakephpWeb ProgrammingPhp
- (05/29) CakePHP 3 - Accept JSON Header Only Working When Debug FalseTAGS:Web ProgrammingCakephpCake3
- (05/23) Remove All Events from Google Calendar (Reset Calendar)TAGS:Web ProgrammingPhp
- (11/08) Google Tag Manager (GTM) Not Firing Default PageView EventTAGS:Web ProgrammingJavascriptGoogle Tag Manager
- (10/13) In PHP, how do you get __toString() Magic Method Result without calling echo?TAGS:CakephpCake3Cakephp 13PhpWeb Programming
- (11/14) Getting output from shell_exec() at all timesTAGS:Web ProgrammingWeb Server Admin
Subscribe to my feed
MainelyDesign.com Blog
Pagination with MSSQL in CakePHP
Posted on 05/07/2009 at 04:36 pm by Kevin Wentworth
Viewed 16,913 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):
- 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:
- 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).
Cheers,
-Kevin Wentworth
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!