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
