Ok i worked on a pear pager wrapper method, it is possibly overkill
still, so need to optimize a little. Here is how i did it, i couldnt
work out how to reset the Zend_Select object so i left the count query
regex asis.
function rewriteCountQuery($sql)
{
if (preg_match('/^\s*SELECT\s+DISTINCT/is', $sql) ||
preg_match('/\s+GROUP\s+BY\s+/is', $sql)) {
return false;
}
$queryCount = preg_replace('/(.|\n)*?\s+FROM\s+/is', 'SELECT
COUNT(*) FROM ', $sql, 1);
list($queryCount, ) = preg_split('/\s+ORDER\s+BY\s+/is', $queryCount);
list($queryCount, ) = preg_split('/\s+LIMIT\s+/is', $queryCount);
return trim($queryCount);
}
function Pager_Wrapper_ZendSelect(&$db, $select, $pager_options =
array(), $disabled = false, $fetchMode = MDB2_FETCHMODE_ASSOC)
{
if (!array_key_exists('totalItems', $pager_options)) {
//be smart and try to guess the total number of records
if ($countQuery = rewriteCountQuery($select->__toString())) {
$totalItems = $db->fetchOne($countQuery);
/*
if (PEAR::isError($totalItems)) {
return $totalItems;
}
*/
} else {
//GROUP BY => fetch the whole resultset and count the rows
returned
$res =& $db->fetchCol($select->__toString());
/*
if (PEAR::isError($res)) {
return $res;
}
*/
$totalItems = count($res);
}
$pager_options['totalItems'] = $totalItems;
}
require_once 'Pager/Pager.php';
$pager = Pager::factory($pager_options);
$page = array();
$page['links'] = $pager->links;
$page['totalItems'] = $pager_options['totalItems'];
$page['page_numbers'] = array(
'current' => $pager->getCurrentPageID(),
'total' => $pager->numPages()
);
list($page['from'], $page['to']) = $pager->getOffsetByPageId();
$page['limit'] = $page['to'] - $page['from'] +1;
if (!$disabled) {
$select->limitPage($pager->getCurrentPageID(),
$pager_options['perPage']);
}
$page['data'] = $select->query()->fetchAll();
/*
if (PEAR::isError($page['data'])) {
return $page['data'];
}
*/
if ($disabled) {
$page['links'] = '';
$page['page_numbers'] = array(
'current' => 1,
'total' => 1
);
}
return $page;
}
$path =
$this->getRequest()->getControllerName().'/'.$this->getRequest()->getActionName();
$page = $this->getRequest()->getParam('page',1);
$pagerOptions = array(
'mode' => 'Sliding',
'delta' => 2,
'perPage' => 15,
'currentPage'=>$page,
'append' => false,
'path'=>'/',
'fileName'=>$path."/page/%d"
);
$pager = Pager_Wrapper_ZendSelect($this->db, $select, $pagerOptions);
$this->view->recordSet = array_chunk($pager['data'], 2);
$this->view->pagerlinks = $pager['links'];
I had no idea how easy it was to column an array using array_chunk damn !
Bill Karwin wrote:
-----Original Message-----
From: Dan Rossi [mailto:[EMAIL PROTECTED]
I was talking view functionality of paging. It seems i also
need to get the count of results first in a different result
before running limit as usual. wonder if thats possible to be
cached so the count isnt called on each request. I may create
a wrapper for pear pager instead.
That's fine, it's a good idea. You could also fetch the whole query
result set and persist it in a Zend_Cache object. Then show slices from
it as needed.
I'm not in favor of putting any data-caching logic in Zend_Db. Metadata
are okay to cache because they (should) change less frequently, but data
are volatile and subject to change. ZF has a robust caching solution,
so you can use that when a given query result set is appropriate to
cache. It should be an application design decision to know which data
are safe to cache, and what is the lifespan of cached data, etc.
Regards,
Bill Karwin
