public function testCreateLimitSubQuery()
{
$query = $this->entityManager->createQuery('SELECT p, c, a FROM DoctrineExtensions\\Paginate\\MyBlogPost p JOIN p.category c JOIN p.author a');
$limitQuery = Paginate::createLimitSubQuery($query, 10, 20);
$this->assertEquals(10, $limitQuery->getFirstResult());
$this->assertEquals(20, $limitQuery->getMaxResults());
$this->assertEquals(array('DoctrineExtensions\\Paginate\\LimitSubqueryWalker'), $limitQuery->getHint(Query::HINT_CUSTOM_TREE_WALKERS));
}
/**
* @param IQueryable $repository
* @return array
*/
public function fetch(IQueryable $repository)
{
$query = $this->getQuery($repository);
if ($this->paginator) {
$query = Paginate::getPaginateQuery($query, $this->paginator->getOffset(), $this->paginator->getLength());
// Step 2 and 3
} else {
$query = $query->setMaxResults(NULL)->setFirstResult(NULL);
}
$this->lastQuery = $query;
return $query->getResult();
}
public function testCreateWhereInQuery()
{
$query = $this->entityManager->createQuery('SELECT u, g FROM DoctrineExtensions\\Paginate\\User u JOIN u.groups g WHERE 1 = 1');
$whereInQuery = Paginate::createWhereInQuery($query, array(1, 2, 3, 4), 'pgid');
$this->assertEquals(array('DoctrineExtensions\\Paginate\\WhereInWalker'), $whereInQuery->getHint(Query::HINT_CUSTOM_TREE_WALKERS));
$this->assertEquals(4, $whereInQuery->getHint('id.count'));
$this->assertEquals('pgid', $whereInQuery->getHint('pg.ns'));
$this->assertEquals(1, $whereInQuery->getParameter('pgid_1'));
$this->assertEquals(2, $whereInQuery->getParameter('pgid_2'));
$this->assertEquals(3, $whereInQuery->getParameter('pgid_3'));
$this->assertEquals(4, $whereInQuery->getParameter('pgid_4'));
}
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);
}
}
/**
* 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);
}
public function testCountQuery_RemovesLimits()
{
$query = $this->entityManager->createQuery('SELECT p, c, a FROM DoctrineExtensions\\Paginate\\BlogPost p JOIN p.category c JOIN p.author a');
$countQuery = Paginate::createCountQuery($query);
$this->assertEquals("SELECT count(DISTINCT b0_.id) AS sclr0 FROM BlogPost b0_ INNER JOIN Category c1_ ON b0_.category_id = c1_.id INNER JOIN Author a2_ ON b0_.author_id = a2_.id", $countQuery->getSql());
}
/**
* 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);
}
/**
* Converts the album properties into tree node properties.
* If the album has sub-albums, the children iterate recursive.
*
* @param Shopware\Models\Media\Album $album
* @return array
*/
private function getAlbumNodeProperties(\Shopware\Models\Media\Album $album)
{
/** @var $repository \Shopware\Models\Media\Repository */
$repository = Shopware()->Models()->Media();
$query = $repository->getAlbumMediaQuery($album->getId());
//returns the total count of the query
$totalResult = \DoctrineExtensions\Paginate\Paginate::count($query);
$node = array(
'id' => $album->getId(),
'text' => $album->getName(),
'position' => $album->getPosition(),
'mediaCount' => $totalResult,
'parentId' => $album->getParentId()
);
//to get fresh album settings from new albums too
$settingsQuery = $repository->getAlbumWithSettingsQuery($album->getId());
$albumData = $settingsQuery->getOneOrNullResult(\Doctrine\ORM\AbstractQuery::HYDRATE_ARRAY);
$settings = $albumData["settings"];
if (!empty($settings) && $settings !== null) {
$node["iconCls"] = $settings["icon"];
$node["createThumbnails"] = $settings["createThumbnails"];
$thumbnails = explode(";",$settings["thumbnailSize"]);
$node["thumbnailSize"] = array();
$count = count($thumbnails);
//convert the thumbnail to an array width the index and value
for ($i = 0; $i <= $count; $i++) {
if ($thumbnails[$i] === '' || $thumbnails[$i] === null) {
continue;
}
$node["thumbnailSize"][] = array('id' => $i,'index' => $i, 'value' => $thumbnails[$i]);
}
}
//has sub-albums, then iterate and add the media count to the parent album.
if (count($album->getChildren()) > 0) {
$node['data'] = $this->toTree($album->getChildren(), $node);
$node['leaf'] = false;
} else {
$node['leaf'] = true;
}
return $node;
}
/**
* 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;
}
/**
* @return Query
*/
protected function createWhereInQuery($ids)
{
return Paginate::createWhereInQuery($this->query, $ids, $this->namepsace);
}