How to use the database mapper with the paginator?
I'm having a bit trouble understanding how I implement the DbSelect paginator using the code below (right now it's using the Iterator adapter which doesn't work for ResultSets).
From what I can tell it's not as straight forward as I would have hoped. DbSelect is expecting a Zend\Db\Sql\Select and an adapter. The adapter is a non issue and can be retrieved with:
$this->newsContents()->getAdapter()
but I'm having trouble getting a Select object out from my TableGateway without duplicating my query code. Is there an easy way to solve this problem?
NewsController.php
<?php
namespace News\Controller;
use Zend\Paginator\Paginator;
class NewsController extends \Application\Controller\WebsiteController
{
protected $newsTable;
protected $newsContents;
protected function newsTable()
{
return $this->getServiceLocator()->get('News\Model\NewsTable');
}
protected function newsContents()
{
return $this->getServiceLocator()->get('News\Model\NewsContentsTable');
}
protected function articleId()
{
return (int) $this->params()->fromRoute('id');
}
public function articleAction()
{
$article = $this->newsTable()->getArticle($this->articleId());
$pages = $this->newsContents()->getPages($this->articleId());
$paginator = new Paginator(new \Zend\Paginator\Adapter\Iterator($pages));
$paginator->setCurrentPageNumber($this->params()->fromRoute('page'));
return array(
'css' => 'news.css',
'article' => $article,
'paginator' => $paginator,
);
}
}
NewsContentsTable.php
<?php
namespace News\Model;
use Zend\Db\Adapter\Adapter;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\TableGateway\AbstractTableGateway;
use Zend\Db\Sql\Select;
class NewsContentsTable extends \Zend\Db\TableGateway\AbstractTableGateway
{
protected $table = 'news_contents';
public function __construct(Adapter $adapter)
{
$this->adapter = $adapter;
$this->resultSetPrototype = new ResultSet;
$this->resultSetPrototype->setArrayObjectPrototype(new NewsContents);
$this->initialize();
}
public function getPages($newsId)
{
$rowset = $this->select(function(Select $select) use ($newsId)
{
$select
->order('order ASC')
->where(array('news_id' => $newsId));
});
return $rowset;
}
}
Implementing paginator with DbSelect as the adapter
// controller
public function articleAction()
{
//...
$paginator = $this->newsContents()->getPages($this->articleId());
$paginator->setCurrentPageNumber($this->params()->fromRoute('page'));
return array(
'css' => 'news.css',
'article' => $article,
'paginator' => $paginator,
);
}
?>
// table
public function getPages($newsId)
{
$sql = $this->getSql();
$select = $sql->select();
$select->where(array('news_id' => $newsId))->order('id ASC');
$adapter = new \Zend\Paginator\Adapter\DbSelect($select, $sql);
$paginator = new \Zend\Paginator\Paginator($adapter);
return $paginator;
}
From Zend Framework 2.2 its much easier (and allows you to fully enjoy the advantages offered by the TableGateway) - you should use
use Zend\Db\TableGateway\TableGateway;
use Zend\Paginator\Paginator;
use Zend\Paginator\Adapter\DbTableGateway; // !!!
$dbTableGatewayAdapter = new DbTableGateway($this->tableGateway);
$paginator = new Paginator($dbTableGatewayAdapter);
return $paginator;
but I'm having trouble getting a Select object out from my TableGateway without duplicating my query code. Is there an easy way to solve this problem?
You can get the select object inside you NewsContentsTable class like this:
$this->getSql()->select();
Use buffer() and next() before return in $rowset.
$rowset->buffer();
$rowset->next();
return $rowset;
Here is sample code for pagination in ZF2
<?php
//var/www/html/zend_app/module/Application/src/Application/Controller/Plugin/MyCustomPlugin.php
namespace Application\Controller\Plugin;
use Zend\View\Helper\AbstractHelper;
use Zend\Db\Adapter\Adapter;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\Sql\Select;
use Zend\Db\Sql\Sql;
use Zend\Db\Sql\Where;
use Zend\Mvc\Controller\Plugin\AbstractPlugin;
use Zend\Paginator\Paginator;
use Zend\Paginator\Adapter\Iterator as paginatorIterator;
use Zend\Paginator\Adapter\DbSelect;
class MyCustomPlugin extends AbstractPlugin {
protected $adapter;
public function __construct(\Zend\Db\Adapter\Adapter $adapter) {
$this->adapter = $adapter;
}
public function getPaginatedTableData($tableName, $whereData = "", $selectedColumn = '' , $currentPageNumber = 1) {
$sql = new Sql($this->adapter);
$select = $sql->select();
if ($selectedColumn) {
$select->columns($selectedColumn);
}
$select->from($tableName);
if ($whereData) {
$select->where($whereData);
}
$paginator = $this->getPaginatorForSelect($select, $currentPageNumber);
$resultSet = new ResultSet;
$resultSet->initialize($paginator);
$resultSet->buffer();
$pagination = array(
'current_page_number'=>$currentPageNumber,
'page_count'=>$paginator->getPages()->pageCount,
'previous'=>isset($paginator->getPages()->previous)?$paginator->getPages()->previous:0,
'next'=>isset($paginator->getPages()->next)?$paginator->getPages()->next:0,
);
return ($resultSet && 0 != $paginator->getPages()->pageCount)?array('resultSet'=>$resultSet->toArray(), 'pagination'=>$pagination):FALSE;
}
public function getPaginatorForSelect($select, $page, $limit=2)
{
$paginatorAdapter = new DbSelect($select, $this->adapter);
$paginator = new Paginator($paginatorAdapter);
$paginator->setItemCountPerPage($limit);
$paginator->setPageRange(5);
$paginator->setCurrentPageNumber($page);
return $paginator;
}
}
HTH
