PHP QueryBuilder::setMaxResults方法代码示例

 /**
  * @param QueryBuilder $queryBuilder
  * @param $callback
  * @param int|null $limit
  * @param int $maxAttempts
  * @throws DBALWalkerException
  * @throws DBALException
  */
 public function run(QueryBuilder $queryBuilder, $callback, $limit = null, $maxAttempts = 10)
 {
     if (!is_callable($callback)) {
         throw new DBALWalkerException('$callback is not callable');
     }
     if (!is_null($limit)) {
         $queryBuilder->setMaxResults($limit);
     } else {
         $limit = $queryBuilder->getMaxResults();
     }
     if (is_null($limit)) {
         $limit = PHP_INT_MAX;
     }
     $offset = 0;
     do {
         $rows = [];
         $errors = $maxAttempts;
         while (true) {
             try {
                 $rows = $queryBuilder->setFirstResult($offset)->execute()->fetchAll();
                 break;
             } catch (DBALException $e) {
                 if ($errors-- == 0) {
                     throw $e;
                 }
                 $queryBuilder->getConnection()->connect();
             }
         }
         call_user_func($callback, $rows, $offset, $limit);
         $offset += $limit;
     } while (count($rows) >= $limit);
 }

 /**
  * @param int $page Starts at 1
  * @param bool $allResults
  * @return DataObjectInterface[]
  */
 public function getResults($page, $allResults = false)
 {
     if ($page < 1 || $page > $this->getPages()) {
         throw new InvalidArgumentException("Page must be between 1 and {$this->getPages()}");
     }
     if (!$allResults) {
         $this->page = $page;
         $this->prev = $page > 1 ? $page - 1 : 0;
         $this->next = $page < $this->pages ? $page + 1 : 0;
         $this->qb->setMaxResults($this->pageSize)->setFirstResult(($page - 1) * $this->pageSize);
     }
     $statement = $this->qb->execute();
     return $statement->fetchAll(\PDO::FETCH_CLASS, $this->class, $this->dependencies);
 }

 /**
  * @internal
  */
 private function incorporateDbalQueryBuilder(QueryBuilder $qb, FilterInterface $filter)
 {
     $criteria = $this->getFilteringCriteria($filter);
     // extraindo os rootAliases, pois o DBAL\QueryBuilder não tem
     $fromPart = $qb->getQueryPart('from');
     $rootAliases = array();
     foreach ($fromPart as $part) {
         $rootAliases[] = $part['alias'];
     }
     $visitor = new DbalQueryExpressionVisitor($qb->getConnection(), $rootAliases, $this->fieldMap);
     if ($whereExpression = $criteria->getWhereExpression()) {
         $qb->andWhere($visitor->dispatch($whereExpression));
         $qb->setParameters($visitor->getParameters());
     }
     if ($criteria->getOrderings()) {
         foreach ($criteria->getOrderings() as $sort => $order) {
             $qb->addOrderBy($visitor->getFieldName($sort), $order);
         }
     }
     if (($firstResult = $criteria->getFirstResult()) !== null) {
         $qb->setFirstResult($firstResult);
     }
     if (($maxResults = $criteria->getMaxResults()) !== null) {
         $qb->setMaxResults($maxResults);
     }
 }

 /**
  * Rewind
  */
 public function rewind()
 {
     //Only perform rewind if current item is not the first element
     if ($this->currentKey !== 0) {
         $this->batchPosition = 0;
         $this->queryBuilder->setFirstResult(0);
         $this->queryBuilder->setMaxResults($this->batchSize);
         /* @var $stmt \Doctrine\DBAL\Statement */
         $stmt = $this->queryBuilder->execute();
         $stmt->setFetchMode(\PDO::FETCH_ASSOC);
         $this->currentItem = null;
         $this->currentKey = -1;
         $this->statement = $stmt;
         $this->next();
     }
 }

 /**
  * Build the data query
  *
  * @return QueryBuilder
  */
 public function getDataQuery()
 {
     $this->buildFilteredQuery();
     if ($this->offset !== null) {
         $this->currentQueryBuilder->setFirstResult($this->offset);
     }
     if ($this->length !== null) {
         $this->currentQueryBuilder->setMaxResults($this->length);
     }
     if ($this->orders !== null) {
         foreach ($this->orders as $order) {
             list($column, $dir) = $order;
             $this->currentQueryBuilder->addOrderBy($column->sourceSort, $dir);
         }
     }
     if (isset($this->conf->group)) {
         $this->currentQueryBuilder->groupBy($this->conf->group);
     }
     return $this->currentQueryBuilder;
 }

 /**
  * @param int $limit
  * @return $this
  */
 public function limit($limit)
 {
     $this->queryBuilder->setMaxResults($limit);
     return $this;
 }

 /**
  * Sets the maximum number of results to retrieve (the "limit").
  *
  * NOTE: Setting max results to "0" will cause mixed behaviour. While most
  * of the databases will just return an empty result set, Oracle will return
  * all entries.
  *
  * @param integer $maxResults The maximum number of results to retrieve.
  *
  * @return \OCP\DB\QueryBuilder\IQueryBuilder This QueryBuilder instance.
  */
 public function setMaxResults($maxResults)
 {
     $this->queryBuilder->setMaxResults($maxResults);
     return $this;
 }

 public function take($rows_count)
 {
     parent::setMaxResults($rows_count);
     return $this;
 }

 /**
  * 加载数据库结果集
  *
  * @param  bool $multiple 是否加载多行数据
  * @return $this|mixed
  */
 protected function _loadResult($multiple = false)
 {
     $this->_dbBuilder->from($this->db()->quoteIdentifier($this->_tableName), $this->db()->quoteIdentifier($this->_objectName));
     // 只获取单条记录
     if (false === $multiple) {
         $this->_dbBuilder->setMaxResults(1);
     }
     // 默认选择所有字段
     $this->_dbBuilder->addSelect($this->_buildSelect());
     // 处理排序问题
     if (!isset($this->_dbApplied['orderBy']) && !empty($this->_sorting)) {
         foreach ($this->_sorting as $column => $direction) {
             if (false === strpos($column, '.')) {
                 // Sorting column for use in JOINs
                 $column = $this->_objectName . '.' . $column;
             }
             $this->_dbBuilder->orderBy($column, $direction);
         }
     }
     if (true === $multiple) {
         $result = $this->_dbBuilder->execute();
         $result->setFetchMode(PDO::FETCH_CLASS, $this->_loadMultiResultFetcherClass(), $this->_loadMultiResultFetcherConstructor());
         $this->reset();
         return $result->fetchAll();
     } else {
         $result = $this->_dbBuilder->execute()->fetch();
         $this->reset();
         if ($result) {
             $this->_loadValues($result);
         } else {
             $this->clear();
         }
         return $this;
     }
 }

 /**
  * @param QueryBuilder $qb
  * @return DataObjectInterface
  */
 protected function fetchOne(QueryBuilder $qb)
 {
     $statement = $qb->setMaxResults(1)->execute();
     $statement->setFetchMode(\PDO::FETCH_CLASS, $this->getClassName(), $this->objectDependencies);
     $object = $statement->fetch();
     if ($object) {
         $this->dispatchEvents('loaded', $object);
         return $object;
     } else {
         return null;
     }
 }

 /**
  * @param QueryBuilder $queryBuilder
  * @return null|object
  */
 public function findOneByQueryBuilder(QueryBuilder $queryBuilder)
 {
     $queryResult = $queryBuilder->setMaxResults(1)->execute()->fetch();
     if (!$queryResult) {
         return null;
     }
     $entity = $this->em->getObject($this->className);
     return $this->hydrate($entity, $queryResult);
 }

 function gridFiltersExt(\Doctrine\DBAL\Query\QueryBuilder &$dbalQuery, $params, $aColumnMapping)
 {
     if (is_array($aColumnMapping) && count($aColumnMapping) > 0) {
         foreach ($aColumnMapping as $value) {
             if ($value["ref"] == $params['sSortBy']) {
                 if (!$value["table"]) {
                     $params['sSortBy'] = $value["col"];
                 } else {
                     $params['sSortBy'] = $value["table"] . "." . $value["col"];
                 }
                 break;
             }
         }
     }
     if (is_array($params['aFilters'])) {
         foreach ($params['aFilters'] as $oFilter) {
             $sFieldname = $oFilter->field;
             if (is_array($aColumnMapping) && count($aColumnMapping) > 0) {
                 foreach ($aColumnMapping as $value) {
                     if ($value["ref"] == $sFieldname) {
                         /**
                          * Update Catalin:
                          * Exista situatii cand nu am nevoie de tabel.coloana ex cand folosesc functii gen GROUP_CONCAT
                          */
                         if (!$value["table"]) {
                             $sFieldname = $value["col"];
                         } else {
                             $sFieldname = $value["table"] . "." . $value["col"];
                         }
                         break;
                     }
                 }
             }
             $sValue = $oFilter->value;
             $sCompare = isset($oFilter->comparison) ? $oFilter->comparison : NULL;
             $sFilterType = $oFilter->type;
             switch ($sFilterType) {
                 case 'string':
                     $dbalQuery->andWhere($sFieldname . " like '%{$sValue}%'");
                     break;
                 case 'list':
                     if (strstr($sValue, ',')) {
                         $aValues = explode(',', $sValue);
                         $this->db->andWhere_in($sFieldname, $aValues);
                     } else {
                         $this->db->andWhere($sFieldname, $sValue);
                     }
                     break;
                 case 'boolean':
                     $this->db->andWhere($sFieldname, $sValue);
                     break;
                 case 'combo':
                     $this->db->andWhere($sFieldname, $sValue);
                     break;
                 case 'numeric':
                     switch ($sCompare) {
                         case 'eq':
                             $dbalQuery->andWhere($sFieldname . '=' . $sValue);
                             break;
                         case 'lt':
                             $dbalQuery->andWhere($sFieldname . '<' . $sValue);
                             break;
                         case 'gt':
                             $dbalQuery->andWhere($sFieldname . '>' . $sValue);
                             break;
                         case 'gte':
                             $dbalQuery->andWhere($sFieldname . '>=' . $sValue);
                             break;
                         case 'lte':
                             $dbalQuery->andWhere($sFieldname . '<=' . $sValue);
                             break;
                     }
                     break;
                 case 'date':
                     switch ($sCompare) {
                         case 'eq':
                             $dbalQuery->andWhere("DATE_FORMAT({$sFieldname}, '%Y-%m-%d')='" . date('Y-m-d', strtotime($sValue)) . "'");
                             break;
                         case 'lt':
                             $dbalQuery->andWhere("DATE_FORMAT({$sFieldname}, '%Y-%m-%d')<='" . date('Y-m-d', strtotime($sValue)) . "'");
                             break;
                         case 'gt':
                             $dbalQuery->andWhere("DATE_FORMAT({$sFieldname}, '%Y-%m-%d')>='" . date('Y-m-d', strtotime($sValue)) . "'");
                             break;
                     }
                     break;
             }
         }
     }
     if (!$params['bIsExport']) {
         $dbalQuery->orderBy($params['sSortBy'], $params['sDir']);
         $dbalQuery->setFirstResult($params['sStart']);
         $dbalQuery->setMaxResults($params['sLimit']);
     } else {
         //Limita maxima de inregistrari
         $dbalQuery->setMaxResults(50000);
     }
 }

 /**
  * Adds pagination parameters to the query.
  *
  * @param QueryBuilder $queryBuilder
  * the query
  * @param $skip
  * the rows to skip
  * @param $amount
  * the maximum amount of rows
  */
 protected function addPagination(QueryBuilder $queryBuilder, $skip, $amount)
 {
     $queryBuilder->setMaxResults(9999999999);
     if ($amount !== null) {
         $queryBuilder->setMaxResults(abs(intval($amount)));
     }
     if ($skip !== null) {
         $queryBuilder->setFirstResult(abs(intval($skip)));
     }
 }

 /**
  * Build up dynamilcy the LIMIT part
  * @param QueryBuilder $qb
  * @param array $limit
  * @return self
  */
 protected function buildLimit(QueryBuilder $qb, array $limit = [])
 {
     switch (count($limit)) {
         case 2:
             $qb->setFirstResult((int) $limit[0]);
             $qb->setMaxResults((int) $limit[1]);
             break;
         case 1:
             $qb->setMaxResults((int) $limit[0]);
             break;
     }
     return $this;
 }

 public function testSetMaxResults()
 {
     $qb = new QueryBuilder($this->conn);
     $qb->setMaxResults(10);
     $this->assertEquals(QueryBuilder::STATE_DIRTY, $qb->getState());
     $this->assertEQuals(10, $qb->getMaxResults());
 }