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(); }
public function testGetsItemsAtOffsetTen() { $expected = $this->qb->setFirstResult(10)->setMaxResults(10)->getQuery()->getResult(); $actual = $this->paginatorAdapter->getItems(10, 10); foreach ($expected as $key => $expectedItem) { $this->assertEquals($expectedItem, $actual[$key]); } }
public function getData(array $p_params, array $p_cols) { $this->_queryObject = $this->_repository->createQueryBuilder('e'); // search if (!empty($p_params['search'])) { $this->search($p_params['search'], $p_cols); } // sort if (@count($p_params['sortCol'])) { $this->sort($p_params, $p_cols); } // limit $this->_queryObject->setFirstResult((int) $p_params['displayStart'])->setMaxResults((int) $p_params['displayLength']); return $this->_queryObject->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); }
/** * @param int $offset * * @return Builder */ public function offset($offset) { if ($offset > 0) { $this->select->setFirstResult($offset); } return $this; }
/** * {@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')); } }
/** * @param QueryBuilder $query * @param int $per_page * @param int $current_page * * @return Configuration */ public function paginateQuery(QueryBuilder $query, $per_page, $current_page = 1) { $counter = clone $query; $total = $counter->select(sprintf('COUNT(%s)', current($query->getRootAliases())))->getQuery()->getSingleScalarResult(); $query->setFirstResult(($current_page - 1) * $per_page)->setMaxResults($per_page); return (new Configuration(ceil($total / $per_page), $current_page))->setMaxNavigate($this->max_navigate); }
/** * Custom findBy so we can filter by related entities * * @param QueryBuilder $qb * @param array $criteria * @param array $orderBy * @param int $limit * @param int $offset * * @return QueryBuilder */ protected function attachCriteriaToQueryBuilder(QueryBuilder $qb, $criteria, $orderBy, $limit, $offset) { if (count($criteria)) { foreach ($criteria as $key => $value) { $values = is_array($value) ? $value : [$value]; $qb->andWhere($qb->expr()->in("a.{$key}", ":{$key}")); $qb->setParameter(":{$key}", $values); } } if (empty($orderBy)) { $orderBy = ['user' => 'ASC']; } if (is_array($orderBy)) { foreach ($orderBy as $sort => $order) { $qb->addOrderBy('a.' . $sort, $order); } } if ($offset) { $qb->setFirstResult($offset); } if ($limit) { $qb->setMaxResults($limit); } return $qb; }
/** * Paging. * Construct the LIMIT clause for server-side processing SQL query. * * @return $this */ private function setLimit() { if (isset($this->requestParams['start']) && -1 != $this->requestParams['length']) { $this->qb->setFirstResult($this->requestParams['start'])->setMaxResults($this->requestParams['length']); } return $this; }
/** * 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; }
/** * 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; }
/** * 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; }
public static function apply(QueryBuilder $qb, $page, $perPage) { if ($perPage > self::MAX_PER_PAGE) { $perPage = self::MAX_PER_PAGE; } $qb->setFirstResult($page * $perPage)->setMaxResults($perPage); return $qb; }
/** * @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); } } }
public function __construct(QueryBuilder $qb, $limit, $page = 1) { $offset = ($page - 1) * $limit; $qb->setFirstResult($offset)->setMaxResults($limit); $this->page = $page; $this->limit = $limit; parent::__construct($qb); }
/** * @param QueryBuilder $qb * @param int|null $offset * @param int|null $limit */ protected function limit(QueryBuilder $qb, int $offset = null, int $limit = null) { if ($offset !== null) { $qb->setFirstResult($offset); } if ($limit !== null) { $qb->setMaxResults($limit); } }
/** * {@inheritdoc} */ public function applyToCollection(ResourceInterface $resource, QueryBuilder $queryBuilder) { $request = $this->requestStack->getCurrentRequest(); if (null === $request || !$this->isPaginationEnabled($resource, $request)) { return; } $itemsPerPage = $this->getItemsPerPage($resource, $request); $queryBuilder->setFirstResult(($this->getPage($resource, $request) - 1) * $itemsPerPage)->setMaxResults($itemsPerPage); }
/** * @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; }
protected function preparePagination(QueryBuilder $qb, PaginationInterface $condition) { if ($condition->getOffset()) { $qb->setFirstResult($condition->getOffset()); } if ($condition->getLimit()) { $qb->setMaxResults($condition->getLimit()); } }
/** * Add Limit * * @param QueryBuilder $queryBuilder * @param Request $request * @param TableModel $table * * @return QueryBuilder */ public function addLimit(QueryBuilder $queryBuilder, Request $request, TableModel $table) { $limit = $request->query->getInt('limit', $table->getLimit()); $page = $request->query->getInt('page', $table->getPage()); $table->setLimit($limit); if (0 < $limit) { $queryBuilder->setFirstResult(($page - 1) * $limit)->setMaxResults($limit); } return $queryBuilder; }
/** * Create query based on current settings * * @param null $offset * @param null $itemCountPerPage * * @return QueryBuilder */ public function createQuery($offset = null, $itemCountPerPage = null) { $service = $this->getService(); $alias = $service->getAlias(); $entity = $service->getEntityClass(); //gets custom query if set if (!($this->_qb = $this->getGrid()->getCustomQueryBuilder())) { $this->_qb = $this->getService()->getEntityManager()->createQueryBuilder(); } $this->_qb->select($alias); $this->_qb->from($entity, $alias); if ($itemCountPerPage) { $this->_qb->setMaxResults($itemCountPerPage); } if ($offset) { $this->_qb->setFirstResult($offset); } $filter = $this->getFilter(); if (is_array($filter) && array_key_exists('field', $filter) && array_key_exists('value', $filter) && array_key_exists('expression', $filter) && array_key_exists('options', $filter)) { $this->filter($filter['field'], $filter['value'], $filter['expression'], $filter['options']); } if ($treeFilter = $this->getTreeFilter()) { $this->buildTreeFilterQuery($treeFilter); } $sort = $this->getSort(); if (is_array($sort)) { $c = 0; foreach ($sort as $s) { if (isset($s['sidx'])) { $field = $s['sidx']; $direction = isset($s['sord']) ? $s['sord'] : 'asc'; if ($c) { $this->_qb->addOrderBy($this->getService()->getAlias() . '.' . $field, $direction); } else { $this->_qb->orderBy($this->getService()->getAlias() . '.' . $field, $direction); } $c++; } } } return $this->_qb; }
public function createView(Request $request) { if ($request->get('page')) { $this->page = $request->get('page'); if (null !== $this->queryBuilder) { $this->queryBuilder->setFirstResult(($this->page - 1) * $this->maxResult); } if (null !== $this->getIterable()) { $this->getIterable()->setInitialOffset(($this->page - 1) * $this->maxResult); if (null !== $this->queryBuilder) { $this->getIterable()->setOffset($this->getIterable()->getInitialOffset()); } } } else { $this->page = 1; } if ($this->infiniteGrid) { $this->perPage = $this->maxResult; } if (null !== $this->getIterable()) { //$this->queryBuilder->setMaxResults($this->maxResult + 1); $this->resultset = $this->getIterable()->current(); //$this->resultset = $this->queryBuilder->getQuery()->getResult(); } else { $this->resultset = array(); } $this->rlength = count($this->resultset); $this->rstart = $this->page * $this->maxResult / $this->perPage; $this->rlast = $this->rlength < $this->maxResult; $this->rpage = (int) ($this->rlength / $this->perPage - ($this->rlength - $this->maxResult > 0 ? 1 : 0)); $this->rpage = $this->rpage > 0 ? $this->rpage : 0; if ($this->routeParams) { foreach ($this->routeParams as $val) { $a[$val] = $request->get($val); } $this->routeParams = $a; } else { $this->routeParams = array(); } return $this; }
/** * Create query object * * @return \Doctrine\ORM\QueryBuilder */ public function createQuery() { $offset = ($this->_options->getPage() - 1) * $this->_options->getRows(); if (!($this->_qb = $this->_options->getCustomQueryBuilder())) { $this->_qb = $this->getEntityManager()->createQueryBuilder(); } $this->_qb->select($this->getAlias()); $this->_qb->from($this->getEntityClass(), $this->getAlias()); if ($joins = $this->getOptions()->getJoins()) { foreach ($joins as $index => $join) { $placeHolder = ':p' . $join['field'] . $index; $this->_qb->innerJoin($this->getAlias() . '.' . $join['field'], $join['alias'])->andWhere($join['alias'] . '.id = ' . $placeHolder)->setParameter($placeHolder, $join['key']); } } if ($itemCountPerPage = $this->_options->getRows()) { $this->_qb->setMaxResults($itemCountPerPage); } if ($offset) { $this->_qb->setFirstResult($offset); } if ($presets = $this->_options->getPresets()) { $this->addPresets($presets); } $filter = $this->_options->getFilters(); if (is_array($filter) && array_key_exists('field', $filter) && array_key_exists('value', $filter) && array_key_exists('expression', $filter) && array_key_exists('options', $filter)) { $this->filter($filter['field'], $filter['value'], $filter['expression'], $filter['options']); } if ($treeFilter = $this->_options->getTreeFilter()) { $this->buildTreeFilterQuery($treeFilter); } $sort = $this->_options->getSortOrder(); if (is_array($sort)) { $c = 0; foreach ($sort as $s) { if (!empty($s['sidx'])) { $field = $s['sidx']; $direction = isset($s['sord']) ? $s['sord'] : 'asc'; if ($c) { $this->_qb->addOrderBy($this->getAlias() . '.' . $field, $direction); } else { $this->_qb->orderBy($this->getAlias() . '.' . $field, $direction); } $c++; } } } if ($subGridFilter = $this->_options->getSubGridFilter()) { foreach ($subGridFilter as $field => $value) { $this->_qb->andWhere($this->_qb->expr()->eq($this->getAlias() . '.' . $field, $value)); } } return $this->_qb; }
/** * Generate paginated output * * @param \Doctrine\ORM\QueryBuilder $qb * @param int $limit * @param int $offset * * @return \FOS\RestBundle\View\View */ protected function paginate(\Doctrine\ORM\QueryBuilder $qb, $limit = null, $offset = null) { if ($offset != null && intval($offset) > 0) { $qb->setFirstResult($offset); } if ($limit != null && intval($limit) > 0) { $qb->setMaxResults($limit); } $paginator = new Paginator($qb, $fetchJoinCollection = true); $view = $this->createView($paginator->getQuery()->getResult()); $view->setHeader('X-Pagination-Total-Results', count($paginator)); return $view; }
/** * @param QueryBuilder $qb * @param array $options * * @return QueryBuilder */ protected function applyQueryOptions(QueryBuilder $qb, array $options) { if (isset($options['identifiers']) && is_array($options['identifiers']) && !empty($options['identifiers'])) { $qb->andWhere('f.code in (:codes)'); $qb->setParameter('codes', $options['identifiers']); } if (isset($options['limit'])) { $qb->setMaxResults((int) $options['limit']); if (isset($options['page'])) { $qb->setFirstResult((int) $options['limit'] * ((int) $options['page'] - 1)); } } return $qb; }
/** * @param Query|QueryBuilder $query * @param int $page * @param int $limit * @param Closure $callback * * @return array */ public function getPaginatedResult($query, $page, $limit, $callback = null) { $query->setFirstResult(($page - 1) * $limit)->setMaxResults($limit); if ($query instanceof QueryBuilder) { $query = $query->getQuery(); } $items = $query->getResult(); if ($callback == null) { $callback = function ($item) { return ['id' => $item->getId(), 'text' => (string) $item]; }; } $data = array_map($callback, $items); $more = count($data) >= $limit; return ['items' => $data, 'itemsPerPage' => $limit, 'more' => $more]; }
/** * {@inheritdoc} */ public function applyToCollection(QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, string $operationName = null) { $request = $this->requestStack->getCurrentRequest(); if (null === $request) { return; } $resourceMetadata = $this->resourceMetadataFactory->create($resourceClass); if (!$this->isPaginationEnabled($request, $resourceMetadata, $operationName)) { return; } $itemsPerPage = $resourceMetadata->getCollectionOperationAttribute($operationName, 'pagination_items_per_page', $this->itemsPerPage, true); if ($resourceMetadata->getCollectionOperationAttribute($operationName, 'pagination_client_items_per_page', $this->clientItemsPerPage, true)) { $itemsPerPage = (int) $request->query->get($this->itemsPerPageParameterName, $itemsPerPage); $itemsPerPage = null !== $this->maximumItemPerPage && $itemsPerPage >= $this->maximumItemPerPage ? $this->maximumItemPerPage : $itemsPerPage; } $queryBuilder->setFirstResult(($request->query->get($this->pageParameterName, 1) - 1) * $itemsPerPage)->setMaxResults($itemsPerPage); }
/** * @param QueryBuilder $builder * @param AbstractFilter $filter * @param $groupBy * * @return array|Paginator */ public function returnFilteredEntities(QueryBuilder $builder, AbstractFilter $filter, $groupBy) { $pagination = $filter->getPagination(); if ($pagination->getLimit()) { $builder->setMaxResults($pagination->getLimit()); } $builder->setFirstResult($pagination->getOffset()); if ($pagination->isEnabled()) { $query = $builder->getQuery(); $query->setHydrationMode($filter->getHydrationMode()); $paginator = new Paginator($query, true); $pagination->setTotalResults(count($paginator)); return $paginator; } $builder->groupBy($groupBy); $query = $builder->getQuery(); $entities = $query->getResult($filter->getHydrationMode()); return $entities; }
/** * Query results after filtering. * * @param integer $rootEntityIdentifier * @param bool $buildQuery * * @return int */ private function getCountFilteredResults($rootEntityIdentifier, $buildQuery = true) { if (true === $buildQuery) { $qb = $this->em->createQueryBuilder(); $qb->select('count(distinct ' . $this->tableName . '.' . $rootEntityIdentifier . ')'); $qb->from($this->entity, $this->tableName); $this->setLeftJoins($qb); $this->setWhere($qb); $this->setWhereAllCallback($qb); return (int) $qb->getQuery()->getSingleScalarResult(); } else { $this->qb->setFirstResult(null)->setMaxResults(null)->select('count(distinct ' . $this->tableName . '.' . $rootEntityIdentifier . ')'); if (true === $this->isPostgreSQLConnection) { $this->qb->groupBy($this->tableName . '.' . $rootEntityIdentifier); return count($this->qb->getQuery()->getResult()); } else { return (int) $this->qb->getQuery()->getSingleScalarResult(); } } }