ZF-10659: Zend_Paginator_Adapter_DbSelect incorrect items on the last page with mssql
Description
Last page of paginator doesn't work correctly with mssql/sqlsrv driver. On the last page it displays items from previous page as well. For example if we have 2 pages (70 rows total): 1. (1-50) 2. (50-70) Then second page will display rows 20-70, not 50-70.
Code sample: $select = ..some select from foo.. $adapter = new Zend_Paginator_Adapter_DbSelect($select); $paginator = new Zend_Paginator($adapter); $paginator->setItemCountPerPage(50); $paginator->setCurrentPageNumber($this->_getParam('page'));
Reason: mssql limit function workaround(e.g. select top *(select top *(query)desc ) asc) (see Db/Adapter/Pdo/Mssql.php )
How to fix:
class Zend_Paginator_Adapter_DbSelect
{
public function getItems($offset, $itemCountPerPage)
{
//check only for mssql, but actually it will be usefull for all db-s
$adapter = $this->_select->getAdapter();
if( $adapter instanceof Zend_Db_Adapter_Pdo_Mssql ||
$adapter instanceof Zend_Db_Adapter_Sqlsrv ){
if( $offset + $itemCountPerPage > $this->_rowCount ){
$itemCountPerPage = $this->_rowCount - $offset;
}
}
$this->_select->limit($itemCountPerPage, $offset);
return $this->_select->query()->fetchAll();
}
}
After fix query for last page will "select top (totalRows-pagesCount*itemPerpage)" and works as expected.
Comments
Posted by Marc Hodgins (mjh_ca) on 2011-03-05T01:29:27.000+0000
Related to ZF-10455
Posted by Maxime Aoustin (maxwell2022) on 2011-04-28T03:42:20.000+0000
I personally think it's better to fix the bug directly inside Zend_Paginator, as the object should return what we are asking for (X results (or less for the last page) per page) and not be dependant of the returned values from Zend_Paginator_Adapter_DbSelect. Zend_Paginator knows that for the last page it will have to return Z results, it should specify the limit to Zend_Paginator_Adapter_DbSelect calculating the new limit. Plus if we are waiting for a fix from MSSQL (related issue ZF-10455) we can wait a long time...