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