Re: [fw-db] Issue with Zend_Db_Table_Select Pagination and Sqlsrv

First, as Pat said, this is a Zend_Db problem, not a Zend_Paginator
problem. Extending your database adapter and overriding the limit
function is one option. Please file a bug if one does not already
exist.

Second, knowing that these sorts of problems would occur, custom
adapters for Zend_Paginator were
designed to be quite simple to implement--just two methods. You could
probably mangle the query object in the adapter to achieve the desired
result.

Of these, the first is the best solution.

-Matt

On Saturday, September 18, 2010, patf <patfigel@gmail.com> wrote:
>
> Hi,
>
> emulating LIMIT and OFFSET for SQL Server is quite tricky.
> ZF generates the following SQL for SQL Server for limit=10 and offset=20:
>
> SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 30 "foobar".* FROM "foobar"
> ORDER BY "foobar" ASC) AS inner_tbl ORDER BY "foobar" DESC) AS outer_tbl
> ORDER BY "foobar" asc
>
> Though this will not work for the last result set ...
>
> This could be fixed in Sqlsrv Adapter by using MSSQL's ROW_NUMBER(), but
> there are two big problems with that solution:
>  - ROW_NUMBER() was introduced with SQL Server 2005, so SQL Server 2000
> would no longer be supported by ZF.
>  - ROW_NUMBER()'s performance is quite weak.
> Plus, the code in Zend_Db_Adapter_Sqlsrv::limit would get even weirder ...
>
> Perhaps you could create a new Paginator Adapter and write some ugly hacks
> to get this working, but I don't think it's worthwhile.
> --
> View this message in context: http://zend-framework-community.634137.n4.nabble.com/Issue-with-Zend-Db-Table-Select-Pagination-and-Sqlsrv-tp2544029p2545610.html
> Sent from the Zend DB mailing list archive at Nabble.com.
>