PHP QueryBuilder::setMaxResults方法代码示例

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

 /**
  * Get the query result
  *
  * @param integer $limit
  *
  * @return ArrayCollection
  */
 public function getQueryResults($limit = null)
 {
     if (isset($limit)) {
         $this->queryBuilder->setMaxResults($limit);
     }
     if (!$this->queryBuilder->getDQLPart('orderBy')) {
         $this->queryBuilder->orderBy('a.createdAt', 'ASC');
     }
     return $this->queryBuilder->getQuery()->getResult();
 }

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

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

 public function paginate(QueryBuilder $query, $limit, $page)
 {
     $query->setMaxResults($limit)->setFirstResult($limit * ($page - 1));
     $paginator = new Paginator($query->getQuery());
     $paginator->setUseOutputWalkers(false);
     return $paginator;
 }

 /**
  * @param QueryBuilder $queryBuilder
  * @param Request      $request
  *
  * @return object[]|Photo[]
  */
 public function getPhotos(QueryBuilder $queryBuilder, Request $request)
 {
     $limit = $request->query->getInt('limit', 10);
     $page = $request->query->getInt('page', 1);
     $offset = $limit * ($page - 1);
     return $queryBuilder->setMaxResults($limit)->setFirstResult($offset)->getQuery()->execute();
 }

 /**
  * Build and returns Query for select rows
  * @param \Doctrine\ORM\QueryBuilder $queryBuilder
  * @param \EMC\TableBundle\Provider\QueryConfigInterface $queryConfig
  * @param array $mapping
  * @return \Doctrine\ORM\Query
  */
 private function getQueryRows(QueryBuilder $queryBuilder, QueryConfigInterface $queryConfig, array &$mapping)
 {
     $queryBuilder->resetDQLPart('select');
     $limit = $queryConfig->getLimit();
     $page = $queryConfig->getPage();
     $select = $queryConfig->getSelect();
     $orderBy = $queryConfig->getOrderBy();
     if ($limit > 0) {
         $queryBuilder->setMaxResults($limit)->setFirstResult(($page - 1) * $limit);
     }
     $mapping = array_map(function ($i) {
         return 'col' . $i;
     }, array_flip($select));
     foreach ($mapping as $column => $name) {
         $queryBuilder->addSelect($column . ' AS ' . $name);
     }
     if (count($orderBy) === 0) {
         $queryBuilder->orderBy($queryBuilder->getRootAlias() . '.id', 'ASC');
     } else {
         foreach ($orderBy as $column => $isAsc) {
             $queryBuilder->orderBy($column, $isAsc ? 'ASC' : 'DESC');
         }
     }
     return $queryBuilder->getQuery();
 }

 /**
  * {@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'));
     }
 }

 /**
  * 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;
 }

 /**
  * 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;
 }

 /**
  * 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;
 }

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

 /**
  * @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);
         }
     }
 }

 /**
  * @param $columns \Sorien\DataGridBundle\Grid\Column\Column[]
  * @param $page int Page Number
  * @param $limit int Rows Per Page
  * @return \Sorien\DataGridBundle\Grid\Rows
  */
 public function execute($columns, $page = 0, $limit = 0)
 {
     $this->query = $this->manager->createQueryBuilder($this->class);
     $this->query->from($this->class, self::TABLE_ALIAS);
     $where = $this->query->expr()->andx();
     $sorted = false;
     foreach ($columns as $column) {
         $this->query->addSelect($this->getFieldName($column));
         if ($column->isSorted() && !$column->isDefaultSort()) {
             $this->query->orderBy($this->getFieldName($column, false), $column->getOrder());
             $sorted = true;
         } elseif (!$sorted && $column->isSorted() && $column->isDefaultSort()) {
             $this->query->orderBy($this->getFieldName($column, false), $column->getOrder());
         }
         if ($column->isFiltered()) {
             if ($column->getFiltersConnection() == column::DATA_CONJUNCTION) {
                 foreach ($column->getFilters() as $filter) {
                     $operator = $this->normalizeOperator($filter->getOperator());
                     $where->add($this->query->expr()->{$operator}($this->getFieldName($column, false), $this->normalizeValue($filter->getOperator(), $filter->getValue())));
                 }
             } elseif ($column->getFiltersConnection() == column::DATA_DISJUNCTION) {
                 $sub = $this->query->expr()->orx();
                 foreach ($column->getFilters() as $filter) {
                     $operator = $this->normalizeOperator($filter->getOperator());
                     $sub->add($this->query->expr()->{$operator}($this->getFieldName($column, false), $this->normalizeValue($filter->getOperator(), $filter->getValue())));
                 }
                 $where->add($sub);
             }
             $this->query->where($where);
         }
     }
     foreach ($this->joins as $alias => $field) {
         $this->query->leftJoin($field, $alias);
     }
     if ($page > 0) {
         $this->query->setFirstResult($page * $limit);
     }
     if ($limit > 0) {
         $this->query->setMaxResults($limit);
     }
     //call overridden prepareQuery or associated closure
     $query = $this->prepareQuery(clone $this->query);
     $items = $query->getQuery()->getResult();
     // hydrate result
     $result = new Rows();
     foreach ($items as $item) {
         $row = new Row();
         foreach ($item as $key => $value) {
             $row->setField($key, $value);
         }
         //call overridden prepareRow or associated closure
         if (($modifiedRow = $this->prepareRow($row)) != null) {
             $result->addRow($modifiedRow);
         }
     }
     return $result;
 }