function index() {
$query = $this->em->createQuery('SELECT p, t, u FROM models\Event p LEFT JOIN p.tags t LEFT JOIN p.members u ORDER BY p.created_at DESC');
$count = Paginate::getTotalQueryResults($query);
$per_page = 5;
$this->load->library('pagination', array('total' => $count, 'base_url' => 'events/index', 'per_page' => $per_page));
$offset = $this->uri->segment(3);
if (!$offset) $offset = 0;
$paginateQuery = Paginate::getPaginateQuery($query, $offset, $per_page);
$posts = $paginateQuery->getResult();
$data['events'] = $posts;
$data['pagination'] = $this->pagination->create_links();
$this->template->title('Events');
$this->template->build('events', $data);
}
function index() {
$this->load->library('mymarkdown');
$query = $this->em->createQuery("SELECT p, t FROM models\Post p LEFT JOIN p.tags t WHERE p.flag IS NULL ORDER BY p.created_at DESC");
$count = Paginate::getTotalQueryResults($query);
$per_page = 5;
$this->load->library('pagination', array('total' => $count, 'base_url' => 'posts/index', 'per_page' => $per_page));
$offset = $this->uri->segment(3);
if (!$offset) $offset = 0;
$paginateQuery = Paginate::getPaginateQuery($query, $offset, $per_page); // Step 2 and 3
$posts = $paginateQuery->getResult();
$data['posts'] = $posts;
$data['pagination'] = $this->pagination->create_links();
$this->template->title('Articles');
$this->template->build('posts', $data);
}
function index() {
$user = models\Current_User::user();
if ($user) {
$query = $this->em->createQuery('SELECT f FROM models\Favorite f WHERE f.user = ?1 ORDER BY f.created_at DESC');
$query->setParameter(1, $user->getId());
$count = Paginate::getTotalQueryResults($query);
$per_page = 5;
$this->load->library('pagination', array('total' => $count, 'base_url' => 'favorites/index', 'per_page' => $per_page));
$offset = $this->uri->segment(3);
if (!$offset) $offset = 0;
$paginateQuery = Paginate::getPaginateQuery($query, $offset, $per_page);
$favorites = $paginateQuery->getResult();
$data['favorites'] = $favorites;
$data['pagination'] = $this->pagination->create_links();
$this->template->title('Favorites');
$this->template->build('favorites', $data);
}
}
/**
* @param IQueryable $repository
* @return integer
*/
public function count(IQueryable $repository)
{
return Paginate::getTotalQueryResults($this->getQuery($repository));
}
/**
* Paginated resultset in ext direct format
*
* @param Query $query
*
* @return array data in ext direct format
*/
public function toArray()
{
if ($this->params->has('sort')) {
$this->addSort();
}
$this->limit = $this->params->getInt('limit', 10);
$this->start = $this->params->getInt('start', 0);
if ($this->params->has('page') && $this->params->get('page') > 0) {
$offset = ($this->params->get('page') - 1) * $this->limit;
} else {
$offset = 0;
}
$query = $this->qb->getQuery();
if ($this->limit != 0) {
$this->count = Paginate::getTotalQueryResults($query);
$paginateQuery = Paginate::getPaginateQuery($query, $offset, $this->limit);
$this->entities = $paginateQuery->getResult();
} else {
$this->entities = $query->getResult();
$this->count = count($this->entities);
}
return $this->dumper->dump($this)->toArray();
}
/**
* Returns query builder instance for retrieving a list of objects with a given where clause and pagination parameters.
*
* @param Doctrine\ORM\QueryBuilder $qb Query builder to be enhanced.
* @param integer $currentPage Where to start selection
* @param integer $resultsPerPage Amount of items to select
*
* @return array Created query instance and amount of affected items.
*/
public function getSelectWherePaginatedQuery(QueryBuilder $qb, $currentPage = 1, $resultsPerPage = 25)
{
$qb = $this->addCommonViewFilters($qb);
$query = $this->getQueryFromBuilder($qb);
$offset = ($currentPage - 1) * $resultsPerPage;
// count the total number of affected items
$count = Paginate::getTotalQueryResults($query);
// prefetch unique relationship ids for given pagination frame
$query = Paginate::getPaginateQuery($query, $offset, $resultsPerPage);
return array($query, $count);
}
/**
* Select with a given where clause and pagination parameters.
*
* @param string $where The where clause to use when retrieving the collection (optional) (default='').
* @param string $orderBy The order-by clause to use when retrieving the collection (optional) (default='').
* @param integer $currentPage Where to start selection
* @param integer $resultsPerPage Amount of items to select
* @param boolean $useJoins Whether to include joining related objects (optional) (default=true).
*
* @return Array with retrieved collection and amount of total records affected by this query.
*/
public function selectWherePaginated($where = '', $orderBy = '', $currentPage = 1, $resultsPerPage = 25, $useJoins = true)
{
$query = $this->_intBaseQuery($where, $orderBy, $useJoins);
$offset = ($currentPage - 1) * $resultsPerPage;
// count the total number of affected items
$count = Paginate::getTotalQueryResults($query);
// prefetch unique relationship ids for given pagination frame
$query = Paginate::getPaginateQuery($query, $offset, $resultsPerPage);
$result = $query->getResult();
return array($result, $count);
}
/**
* {@inheritdoc }
*/
public function getTotalResults()
{
return (int) Paginate::getTotalQueryResults($this->query);
}
/**
* Returns query builder instance for retrieving a list of objects with a given where clause and pagination parameters.
*
* @param Doctrine\ORM\QueryBuilder $qb Query builder to be enhanced.
* @param integer $currentPage Where to start selection
* @param integer $resultsPerPage Amount of items to select
*
* @return array Created query instance and amount of affected items.
*/
public function getSelectWherePaginatedQuery(QueryBuilder $qb, $currentPage = 1, $resultsPerPage = 25)
{
$qb = $this->addCommonViewFilters($qb);
$query = $this->getQueryFromBuilder($qb);
$offset = ($currentPage - 1) * $resultsPerPage;
// count the total number of affected items
$count = Paginate::getTotalQueryResults($query);
$query->setFirstResult($offset)->setMaxResults($resultsPerPage);
return array($query, $count);
}
/**
* Select with a given where clause and pagination parameters.
*
* @param string $where The where clause to use when retrieving the collection (optional) (default='').
* @param string $orderBy The order-by clause to use when retrieving the collection (optional) (default='').
* @param integer $currentPage Where to start selection
* @param integer $resultsPerPage Amount of items to select
* @param boolean $useJoins Whether to include joining related objects (optional) (default=true).
*
* @return Array with retrieved collection and amount of total records affected by this query.
*/
public function selectWherePaginated($where = '', $orderBy = '', $currentPage = 1, $resultsPerPage = 25, $useJoins = true)
{
$query = $this->_intBaseQuery($where, $orderBy, $useJoins);
$offset = ($currentPage - 1) * $resultsPerPage;
// count the total number of affected items
$count = Paginate::getTotalQueryResults($query);
$query->setFirstResult($offset)->setMaxResults($resultsPerPage);
$result = $query->getResult();
return array($result, $count);
}
/**
* Add pagination limits to the query
*
* http://www.mysqlperformanceblog.com/2008/09/24/four-ways-to-optimize-paginated-displays/
*/
private function addPaginationLimitToQuery()
{
// Page (URL alias => p)
$page = $this->getPage();
// Results Per Page (URL alias => rpp)
$resultsPerPage = $this->getResultsPerPage();
// calculate offset = current page
$offset = ($page - 1) * $resultsPerPage;
/**
* DQL does not have LIMIT and OFFSET capability
* so we can't use $this->queryBuilder->add('limit',
* D2 does limiting on hydration level ?
*/
$query = $this->queryBuilder->getQuery();
#$query->setFirstResult( $offset )->setMaxResults( $resultsPerPage );
#Clansuite_Debug::printR($query->getArrayResult());
// Step 1 - count total results
$count = Paginate::getTotalQueryResults($query);
$this->setTotalResultsCount($count);
// Step 2 - adding limit and offset to the query
$paginateQuery = Paginate::getPaginateQuery($query, $offset, $resultsPerPage);
// fetching the paginated results set
#Clansuite_Debug::printR($paginateQuery->getArrayResult());
return $paginateQuery;
}