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