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); }