PHP QueryBuilder::setFirstResult方法代码示例

 public function testGetsItemsAtOffsetTen()
 {
     $expected = $this->qb->setFirstResult(10)->setMaxResults(10)->getQuery()->getResult();
     $actual = $this->paginatorAdapter->getItems(10, 10);
     foreach ($expected as $key => $expectedItem) {
         $this->assertEquals($expectedItem, $actual[$key]);
     }
 }

 public function getItems()
 {
     $this->qb->setMaxResults($this->getLimit());
     $this->qb->setFirstResult($this->getOffset());
     list($sortColumn, $sortType) = $this->getSorting();
     if ($sortColumn) {
         $this->qb->orderBy($this->qb->getRootAlias() . "." . $sortColumn, $sortType);
     }
     return $this->qb->getQuery()->getResult();
 }

 public function getData(array $p_params, array $p_cols)
 {
     $this->_queryObject = $this->_repository->createQueryBuilder('e');
     // search
     if (!empty($p_params['search'])) {
         $this->search($p_params['search'], $p_cols);
     }
     // sort
     if (@count($p_params['sortCol'])) {
         $this->sort($p_params, $p_cols);
     }
     // limit
     $this->_queryObject->setFirstResult((int) $p_params['displayStart'])->setMaxResults((int) $p_params['displayLength']);
     return $this->_queryObject->getQuery()->getResult();
 }

 private function getQuery($filter, $order)
 {
     if (empty($this->queryBuilder)) {
         if (!isset($this->limit)) {
             throw new InvalidStateException('Property limit must be set.');
         }
         if (!isset($this->offset)) {
             throw new InvalidStateException('Property offset must be set.');
         }
         $this->queryBuilder = Callback::invokeArgs($this->callback, [$filter, $order]);
         $this->queryBuilder->setMaxResults($this->limit);
         $this->queryBuilder->setFirstResult($this->offset);
     }
     return new Paginator($this->queryBuilder);
 }

 /**
  * @param int $offset
  *
  * @return Builder
  */
 public function offset($offset)
 {
     if ($offset > 0) {
         $this->select->setFirstResult($offset);
     }
     return $this;
 }

 /**
  * {@inheritdoc}
  */
 public function filter(Request $request, QueryBuilder $queryBuilder)
 {
     if ($request->query->has('per_page') && $request->query->has('page')) {
         $queryBuilder->setMaxResults($request->query->get('per_page'));
         $queryBuilder->setFirstResult(($request->query->get('page') - 1) * $request->query->get('per_page'));
     }
 }

 /**
  * @param QueryBuilder $query
  * @param int $per_page
  * @param int $current_page
  *
  * @return Configuration
  */
 public function paginateQuery(QueryBuilder $query, $per_page, $current_page = 1)
 {
     $counter = clone $query;
     $total = $counter->select(sprintf('COUNT(%s)', current($query->getRootAliases())))->getQuery()->getSingleScalarResult();
     $query->setFirstResult(($current_page - 1) * $per_page)->setMaxResults($per_page);
     return (new Configuration(ceil($total / $per_page), $current_page))->setMaxNavigate($this->max_navigate);
 }

 /**
  * Custom findBy so we can filter by related entities
  *
  * @param QueryBuilder $qb
  * @param array $criteria
  * @param array $orderBy
  * @param int $limit
  * @param int $offset
  *
  * @return QueryBuilder
  */
 protected function attachCriteriaToQueryBuilder(QueryBuilder $qb, $criteria, $orderBy, $limit, $offset)
 {
     if (count($criteria)) {
         foreach ($criteria as $key => $value) {
             $values = is_array($value) ? $value : [$value];
             $qb->andWhere($qb->expr()->in("a.{$key}", ":{$key}"));
             $qb->setParameter(":{$key}", $values);
         }
     }
     if (empty($orderBy)) {
         $orderBy = ['user' => 'ASC'];
     }
     if (is_array($orderBy)) {
         foreach ($orderBy as $sort => $order) {
             $qb->addOrderBy('a.' . $sort, $order);
         }
     }
     if ($offset) {
         $qb->setFirstResult($offset);
     }
     if ($limit) {
         $qb->setMaxResults($limit);
     }
     return $qb;
 }

 /**
  * Paging.
  * Construct the LIMIT clause for server-side processing SQL query.
  *
  * @return $this
  */
 private function setLimit()
 {
     if (isset($this->requestParams['start']) && -1 != $this->requestParams['length']) {
         $this->qb->setFirstResult($this->requestParams['start'])->setMaxResults($this->requestParams['length']);
     }
     return $this;
 }

 /**
  * This method alters the query to return a clean set of object with a working
  * set of Object
  *
  * @param \Doctrine\ORM\QueryBuilder $queryBuilder
  * @return void
  */
 private function getFixedQueryBuilder(QueryBuilder $queryBuilder)
 {
     $queryBuilderId = clone $queryBuilder;
     // step 1 : retrieve the targeted class
     $from = $queryBuilderId->getDQLPart('from');
     $class = $from[0]->getFrom();
     // step 2 : retrieve the column id
     $idName = current($queryBuilderId->getEntityManager()->getMetadataFactory()->getMetadataFor($class)->getIdentifierFieldNames());
     // step 3 : retrieve the different subjects id
     $select = sprintf('%s.%s', $queryBuilderId->getRootAlias(), $idName);
     $queryBuilderId->resetDQLPart('select');
     $queryBuilderId->add('select', 'DISTINCT ' . $select);
     $results = $queryBuilderId->getQuery()->execute(array(), Query::HYDRATE_ARRAY);
     $idx = array();
     $connection = $queryBuilder->getEntityManager()->getConnection();
     foreach ($results as $id) {
         $idx[] = $connection->quote($id[$idName]);
     }
     // step 4 : alter the query to match the targeted ids
     if (count($idx) > 0) {
         $queryBuilder->andWhere(sprintf('%s IN (%s)', $select, implode(',', $idx)));
         $queryBuilder->setMaxResults(null);
         $queryBuilder->setFirstResult(null);
     }
     return $queryBuilder;
 }

 /**
  * Adds common filter options (limit, offset, order) to a query builder
  * @param  QueryBuilder $qb
  * @param  array        $options
  * @return QueryBuilder
  * @author Yohann Marillet
  */
 protected function addCommonFilters(QueryBuilder $qb, array $options = array())
 {
     if (isset($options['_prefix'])) {
         $options['_prefix'] = rtrim($options['_prefix'], '.') . '.';
     } else {
         $options['_prefix'] = '';
     }
     if (isset($options['limit']) && !empty($options['limit'])) {
         $qb->setMaxResults((int) $options['limit']);
         if (isset($options['offset'])) {
             $qb->setFirstResult((int) $options['offset']);
         }
     }
     if (isset($options['order'])) {
         foreach ($options['order'] as $field => $order) {
             if (is_int($field)) {
                 $field = $order;
                 $order = 'ASC';
             }
             $field = $options['_prefix'] . $field;
             $qb->addOrderBy($this->replaceByJoinAlias($field, $qb), $order);
         }
     }
     return $qb;
 }

 /**
  * Set the page for pagination
  *
  * @param $pageNumber
  * @param $itemsPerPage
  * @return $this
  */
 public function limit($pageNumber, $itemsPerPage)
 {
     $offset = $pageNumber <= 1 ? 0 : ($pageNumber - 1) * $itemsPerPage;
     $this->queryBuilder->setMaxResults($itemsPerPage);
     $this->queryBuilder->setFirstResult($offset);
     return $this;
 }

 public static function apply(QueryBuilder $qb, $page, $perPage)
 {
     if ($perPage > self::MAX_PER_PAGE) {
         $perPage = self::MAX_PER_PAGE;
     }
     $qb->setFirstResult($page * $perPage)->setMaxResults($perPage);
     return $qb;
 }

 /**
  * @return mixed
  */
 public function getItems()
 {
     $this->qb->setMaxResults($this->getLimit());
     $this->qb->setFirstResult($this->getOffset());
     if (count($this->getSorting()) > 0) {
         foreach ($this->getSorting() as $sortColumn => $sortType) {
             if (isset($this->columnAliases[$sortColumn])) {
                 $sortColumn = $this->columnAliases[$sortColumn]->qbName;
             } else {
                 $aliases = $this->qb->getRootAliases();
                 $sortColumn = $aliases[0] . '.' . $sortColumn;
             }
             $this->qb->addOrderBy($sortColumn, is_string($sortType) && strncasecmp($sortType, 'd', 1) || $sortType > 0 ? 'ASC' : 'DESC');
         }
     }
     return $this->qb->getQuery()->getResult();
 }

 /**
  * @param int $limit
  * @param int $offset
  * @return array
  */
 public function getData($limit = NULL, $offset = NULL)
 {
     if ($limit !== NULL) {
         $this->builder->setMaxResults($limit);
         $this->builder->setFirstResult($offset);
     }
     $class = $this->DTO;
     if ($class) {
         foreach ($this->builder->getQuery()->getResult($this->resultType) as $item) {
             (yield new $class($item));
         }
     } else {
         foreach ($this->builder->getQuery()->getResult($this->resultType) as $item) {
             (yield $item);
         }
     }
 }