Sets the maximum number of results to retrieve (the "limit").
| public setMaxResults ( integer $maxResults ) | ||
| $maxResults | integer | The maximum number of results to retrieve. |
/**
* @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;
}
/**
* Sets the maximum number of results to retrieve (the "limit").
*
* @param int $maxResults The maximum number of results to retrieve.
*
* @return QueryBuilder This QueryBuilder instance.
*/
public function setMaxResults(int $maxResults) : QueryBuilder
{
$this->concreteQueryBuilder->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());
}
/**
* @test
*/
public function setMaxResultsDelegatesToConcreteQueryBuilder()
{
$this->concreteQueryBuilder->setMaxResults(Argument::cetera())->shouldBeCalled()->willReturn($this->subject);
$this->subject->setMaxResults(1);
}
/**
* Sets the maximum number of results to retrieve (the "limit").
*
* @param integer $maxResults The maximum number of results to retrieve.
*
* @return self
*/
public function setMaxResults($maxResults)
{
$this->qb->setMaxResults($maxResults);
return $this;
}
/**
* @param array $request
* @return array
*/
public function make(array $request)
{
$output = ['data' => [], 'draw' => $request['draw'], 'recordsFiltered' => 0, 'recordsTotal' => 0];
/**
* Order By
*/
if (isset($request['order'])) {
for ($i = 0; $i < count($request['order']); ++$i) {
$j = intval($request['order'][$i]['column']);
if ($request['columns'][$j]['orderable'] != 'true') {
continue;
}
$column = $request['columns'][$j]['data'];
$sort = $request['order'][$i]['dir'];
$this->query->addOrderBy($column, $sort);
}
}
/**
* Count All
*/
$temp = clone $this->query;
$temp->resetQueryPart('select');
$temp->resetQueryPart('orderBy');
$temp->select("COUNT(*)");
$output['recordsTotal'] = $temp->execute()->fetchColumn(0);
/**
* Filter
*/
for ($i = 0; $i < count($request['columns']); ++$i) {
if ($request['columns'][$i]['searchable'] != 'true') {
continue;
}
$value = $request['columns'][$i]['search']['value'];
if (strlen($value) > 0) {
$column = $request['columns'][$i]['data'];
$value = $this->query->getConnection()->quote("{$value}%");
$this->query->andHaving($this->query->expr()->like($column, $value));
}
}
/**
* Search
*/
if (isset($request['search'])) {
$value = $request['search']['value'];
if (strlen($value) > 0) {
for ($i = 0; $i < count($request['columns']); ++$i) {
if ($request['columns'][$i]['searchable'] != 'true') {
continue;
}
$column = $request['columns'][$i]['data'];
$this->query->orHaving($this->query->expr()->like($column, ':search'));
}
$this->query->setParameter('search', "%{$value}%");
}
}
/**
* Count Filtered
*/
$temp = clone $this->query;
$temp->resetQueryPart('orderBy');
$output['recordsFiltered'] = $temp->execute()->rowCount();
/**
* Limit
*/
if (isset($request['start'])) {
$this->query->setFirstResult($request['start']);
}
if (isset($request['length'])) {
$this->query->setMaxResults($request['length']);
}
/**
* Fetch Results
*/
$output['data'] = $this->query->execute()->fetchAll(\PDO::FETCH_ASSOC);
/**
* Add Filter
*/
return $output;
}
/**
* Loads Location data for the given $pass.
*
* @param \Doctrine\DBAL\Query\QueryBuilder $queryBuilder
* @param int $pass
*
* @return array
*/
protected function loadLocationData(QueryBuilder $queryBuilder, $pass)
{
$queryBuilder->setFirstResult($pass * $this->bulkCount);
$queryBuilder->setMaxResults($this->bulkCount);
$statement = $queryBuilder->execute();
$rows = $statement->fetchAll(PDO::FETCH_ASSOC);
return $rows;
}
/**
* Conditionally add LIMIT and ORDER BY to a QueryBuilder query.
*
* @param QueryBuilder $query
* @param array $options Additional options:
* - 'limit' (integer): Maximum number of results to return
* - 'order' (string): Field to order by
* - 'direction' (string): ASC or DESC
* - 'contentid' (integer): Filter further by content ID
* - 'id' (integer): Filter by a specific change log entry ID
*/
protected function setLimitOrder(QueryBuilder $query, array $options)
{
if (isset($options['order'])) {
$query->orderBy($options['order'], $options['direction']);
}
if (isset($options['limit'])) {
$query->setMaxResults(intval($options['limit']));
if (isset($options['offset'])) {
$query->setFirstResult(intval($options['offset']));
}
}
}
protected function fetchPageResults()
{
$this->results = $this->repository->findByQueryBuilder($this->queryBuilder->setMaxResults($this->limit)->setFirstResult(($this->page - 1) * $this->limit));
}
