PhpRiot
News Archive
PhpRiot Newsletter
Your Email Address:

More information

Daniel Cousineau's Blog: Doctrine 1.2 MSSQL Alternative LIMIT/Paging

Note: This article was originally published at PHPDeveloper on 18 September 2010.
PHPDeveloper

Daniel Cousineau has a new post to his blog today looking at an alternative that can be used for pagination in your MSSQL queries than the trick with TOP and reversing the ORDER BY in Doctrine.

As ugly as this technique is, it works. The problem is it requires an extreme amount of intelligence or an extreme amount of simplicity in the query in order for an automated system like Doctrine to be usable. The biggest caveat with this technique is good goddamned luck paging your query if it doesn't have an ORDER BY. And sometimes queries that are complex enough break the modified Zend_Db code. There exists an easier MSSQL paging technique. Using features first available in SQL Server 2005, with only 1 subquery you can mimic MySQL's LIMIT clause with ease.

He includes the query that will make it happen (the SQL for it) and then the implementation as an adapter you can use to get it to cooperate in your Doctrine queries.