Sets the position of the first result to retrieve (the "offset").
public setFirstResult ( integer $firstResult ) | ||
$firstResult | integer | The first result to return. |
/** * @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); }
/** * @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); } }
/** * @see \ComPHPPuebla\Doctrine\Query\Specification::match() */ public function match(QueryBuilder $qb) { if ($this->has('page')) { $this->page = $this->get('page'); $this->initPageSize(); $qb->setFirstResult($this->calculateOffset())->setMaxResults($this->pageSize); } }
/** * Prepare listing instance from Doctrine DBAL QueryBuilder. * * Total rows count for Listing instance must be set separately. * * @param DBALQueryBuilder * @param boolean * @param boolean * @return self */ public function setDBALQuery(DBALQueryBuilder $query, $appendLimit = true, $appendOrder = true) { if ($appendLimit) { $query->setFirstResult($this->offset())->setMaxResults($this->limit()); } if ($appendOrder && $this->order()) { call_user_func_array([$query, 'orderBy'], (array) $this->order()); } $this->setItems($query->execute()->fetchAll()); return $this; }
/** * 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 $offset * @return $this */ public function offset($offset) { $this->queryBuilder->setFirstResult($offset); return $this; }
/** * Sets the position of the first result to retrieve (the "offset"). * * @param int $firstResult The first result to return. * * @return QueryBuilder This QueryBuilder instance. */ public function setFirstResult(int $firstResult) : QueryBuilder { $this->concreteQueryBuilder->setFirstResult($firstResult); return $this; }
public function skip($rows_count) { parent::setFirstResult($rows_count); return $this; }
public function next() { $this->offset += $this->maxresults; $this->qb->setFirstResult($this->offset); }
/** * Sets the position of the first result to retrieve (the "offset"). * * @param integer $firstResult The first result to return. * * @return self */ public function setFirstResult($firstResult) { $this->qb->setFirstResult($firstResult); return $this; }
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 testSetFirstResult() { $qb = new QueryBuilder($this->conn); $qb->setFirstResult(10); $this->assertEquals(QueryBuilder::STATE_DIRTY, $qb->getState()); $this->assertEQuals(10, $qb->getFirstResult()); }
/** * @test */ public function setFirstResultDelegatesToConcreteQueryBuilder() { $this->concreteQueryBuilder->setFirstResult(Argument::cetera())->shouldBeCalled()->willReturn($this->subject); $this->subject->setFirstResult(1); }
/** * Small helper function which adds the first and max result to the query builder. * * @param DBALQueryBuilder $builder * @param $offset * @param $limit * @return $this */ private function addPagination(DBALQueryBuilder $builder, $offset, $limit) { $builder->setFirstResult($offset)->setMaxResults($limit); 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'])); } } }
/** * Applies operation to data source and returns modified data source. * * @param QueryBuilder $src * @param OperationInterface|PaginateOperation $operation * @return QueryBuilder */ public function process($src, OperationInterface $operation) { return $src->setFirstResult($this->getOffset($operation))->setMaxResults($operation->getPageSize()); }