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(); }
/** * * @return boolean */ public function process() { $this->form->submit($this->request); if ($this->form->isValid()) { $formData = $this->form->getData(); $name = (string) $formData->getName(); if ($name) { $this->qb->andWhere($this->qb->expr()->like('p.name', ':name'))->setParameter('name', '%' . $name . '%'); } $text = (string) $formData->getText(); if ($text) { $this->qb->andWhere($this->qb->expr()->orx($this->qb->expr()->like('p.intro', ':intro'), $this->qb->expr()->like('p.content', ':content')))->setParameter('intro', '%' . $text . '%')->setParameter('content', '%' . $text . '%'); } $isPublished = (string) $formData->getIsPublished(); if ($isPublished) { if ($isPublished == 'yes') { $this->qb->andWhere($this->qb->expr()->eq('p.status', ':status'))->setParameter('status', PostStatus::PUBLISHED); } if ($isPublished == 'no') { $this->qb->andWhere($this->qb->expr()->neq('p.status', ':status'))->setParameter('status', PostStatus::PUBLISHED); } } $order = (string) $formData->getOrder(); if ($order) { $this->qb->orderBy('p.' . $order, 'DESC'); } return true; } return false; }
/** * Build and returns Query for select rows * @param \Doctrine\ORM\QueryBuilder $queryBuilder * @param \EMC\TableBundle\Provider\QueryConfigInterface $queryConfig * @param array $mapping * @return \Doctrine\ORM\Query */ private function getQueryRows(QueryBuilder $queryBuilder, QueryConfigInterface $queryConfig, array &$mapping) { $queryBuilder->resetDQLPart('select'); $limit = $queryConfig->getLimit(); $page = $queryConfig->getPage(); $select = $queryConfig->getSelect(); $orderBy = $queryConfig->getOrderBy(); if ($limit > 0) { $queryBuilder->setMaxResults($limit)->setFirstResult(($page - 1) * $limit); } $mapping = array_map(function ($i) { return 'col' . $i; }, array_flip($select)); foreach ($mapping as $column => $name) { $queryBuilder->addSelect($column . ' AS ' . $name); } if (count($orderBy) === 0) { $queryBuilder->orderBy($queryBuilder->getRootAlias() . '.id', 'ASC'); } else { foreach ($orderBy as $column => $isAsc) { $queryBuilder->orderBy($column, $isAsc ? 'ASC' : 'DESC'); } } return $queryBuilder->getQuery(); }
/** * @param ClientConditionInterface $condition * @return Client[] */ public function search(ClientConditionInterface $condition) { $this->qb = $this->em->createQueryBuilder(); $this->qb->select('c')->from('CoreBundle:Client', 'c'); $this->initQueryBuilder($condition); $this->preparePagination($this->qb, $condition); $this->qb->orderBy('c.createdAt', 'DESC'); return $this->qb->getQuery()->getResult(); }
/** * Get the query result * * @param integer $limit * * @return ArrayCollection */ public function getQueryResults($limit = null) { if (isset($limit)) { $this->queryBuilder->setMaxResults($limit); } if (!$this->queryBuilder->getDQLPart('orderBy')) { $this->queryBuilder->orderBy('a.createdAt', 'ASC'); } return $this->queryBuilder->getQuery()->getResult(); }
protected function order() { $column = $this->getParamAdapter()->getColumn(); $order = $this->getParamAdapter()->getOrder(); if (!$column) { return; } $header = $this->getTable()->getHeader($column); $tableAlias = $header ? $header->getTableAlias() : 'q'; if ($column) { $this->query->orderBy($tableAlias . '.' . $column, $order); } }
public function sort(array $p_cols) { foreach (array_keys($p_cols) as $id => $property) { if (!is_string($property)) { // not sortable continue; } if (@in_array($id, $p_params['sortCol'])) { $dir = @in_array($id, $p_params['sortDir']) ?: 'asc'; $this->_queryObject->orderBy("e.{$property}", $dir); } } }
/** * Parse order conditions and add it to query * * @param string|null $sort * @param string|null $order */ public function orderBy($sort = null, $order = null) { if (is_string($sort)) { if (strpos($sort, '.') > 0) { $parts = explode('.', $sort); if (count($parts) >= 2 && array_key_exists($parts[0], $this->metadata->associationMappings)) { $this->qBuilder->innerJoin('e.' . $parts[0], $parts[0], Join::WITH); $this->qBuilder->orderBy($parts[0] . '.' . $parts[1], $order); } } else { if (array_key_exists($sort, $this->metadata->fieldMappings)) { $this->qBuilder->orderBy('e.' . $sort, $order); } } } }
/** * @param QueryBuilder $qb * @param string $alias * @param string|null $sortField * @param string|null $sortOrder * @return QueryBuilder */ protected function sorting(QueryBuilder $qb, string $alias, string $sortField = null, string $sortOrder = null) { if (!empty($sortField) && (strtolower($sortOrder) == 'asc' || strtolower($sortOrder) == 'desc')) { $qb->orderBy($alias . '.' . $sortField, $sortOrder); } return $qb; }
public static function apply(QueryBuilder $qb, array $orderBy) { foreach ($orderBy as $field => $sortOrder) { $qb->orderBy('a.' . Camelizer::camelize($field), $sortOrder); } return $qb; }
/** * @param QueryBuilder $qb * @param string $dqlAlias */ public function modify(QueryBuilder $qb, $dqlAlias) { if ($this->dqlAlias !== null) { $dqlAlias = $this->dqlAlias; } $qb->orderBy(sprintf('%s.%s', $dqlAlias, $this->field), $this->order); }
/** * set order * * @param string $order_field * @param string $order_type * * @return Datatable */ public function setOrder($order_field, $order_type) { $this->order_field = $order_field; $this->order_type = $order_type; $this->queryBuilder->orderBy($order_field, $order_type); return $this; }
/** * {@inheritdoc} */ public function filter(Request $request, QueryBuilder $queryBuilder) { $order = $request->query->get('order') ?: self::DEFAULT_ORDER; if ($request->query->has('sort')) { $alias = $queryBuilder->getRootAliases()[0]; $queryBuilder->orderBy($alias . '.' . $request->query->get('sort'), $order); } }
/** * Apply the sortings to the query builder * * @param QueryBuilder $queryBuilder * @param Sort $sortCollectionDefinition * * @return \Doctrine\ORM\QueryBuilder */ public function sort(QueryBuilder $queryBuilder, Sort $sortCollectionDefinition, $uniqueNameToQueryFieldMap) { /** @var $sortDefinition SortCondition */ foreach ($sortCollectionDefinition as $sortDefinition) { $identifier = $uniqueNameToQueryFieldMap[$sortDefinition->getFieldName()]; $queryBuilder->orderBy($identifier, $sortDefinition->getDirection()); } return $queryBuilder; }
/** * @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 applySorting(QueryBuilder $queryBuilder, array $sorting = null) { if (null === $sorting) { return; } $alias = $this->getAlias(); foreach ($sorting as $property => $order) { $queryBuilder->orderBy($alias . '.' . $property, $order); } }
protected function order() { $column = $this->getParamAdapter()->getColumn(); $order = $this->getParamAdapter()->getOrder(); if (!$column) { return; } $header = $this->getTable()->getHeader($column); $tableAlias = $header ? $header->getTableAlias() : 'q'; if (false === strpos($tableAlias, '.')) { $tableAlias = $tableAlias . '.' . $column; } if ($header->getOrderJoin()) { $joinAlias = 'j' . str_replace('.', '_', $tableAlias); $this->query->join($tableAlias, $joinAlias); $this->query->orderBy($joinAlias . '.' . $header->getOrderJoin(), $order); } else { $this->query->orderBy($tableAlias, $order); } }
protected function processOrderByForList(QueryBuilder $qb, $orderBy) { $fieldName = is_array($orderBy) ? key($orderBy) : $orderBy; $order = is_array($orderBy) ? $orderBy[$fieldName] : 'ASC'; if (in_array($fieldName, ['visits', 'visitsCount', 'visitCount'])) { $qb->addSelect('COUNT(v) AS totalVisits')->leftJoin('s.visits', 'v')->groupBy('s')->orderBy('totalVisits', $order); return array_column($qb->getQuery()->getResult(), 0); } elseif (in_array($fieldName, ['originalUrl', 'shortCode', 'dateCreated'])) { $qb->orderBy('s.' . $fieldName, $order); } return $qb->getQuery()->getResult(); }
/** * Add a orderBy clause to the main query * * @param $column * @param $order * @return $this */ public function orderBy($column, $order) { $column = str_replace(".", "", $column); $selects = $this->queryBuilder->getDQLPart('select'); foreach ($selects as $select) { $selectSegments = explode(" ", $select); if ($selectSegments[count($selectSegments) - 1] == $column) { $this->queryBuilder->orderBy($selectSegments[0], $order); } } return $this; }
/** * Load the collection * */ protected function initialize() { if ($this->initialized) { return; } // Handle search if (isset($this->searchExpression)) { $queryBuilderExpression = $this->buildQueryBuilderExpression($this->searchExpression); $this->queryBuilder->andWhere($queryBuilderExpression); } // Handle filters if (isset($this->filterExpression)) { $queryBuilderExpression = $this->buildQueryBuilderExpression($this->filterExpression); $this->queryBuilder->andWhere($queryBuilderExpression); } // Handle sort if (null !== $this->sortField && null !== $this->sortDirection) { $oldOrderBys = $this->queryBuilder->getDQLPart('orderBy'); $this->queryBuilder->resetDQLPart('orderBy'); $this->queryBuilder->orderBy($this->sortField, $this->sortDirection); foreach ($oldOrderBys as $oldOrderBy) { $this->queryBuilder->add('orderBy', $oldOrderBy, true); } } // Handle pagination if (isset($this->limitPerPage)) { $paginator = new DoctrineORMPaginator($this->queryBuilder->getQuery()); $paginator->setLimitPerPage($this->limitPerPage)->setRangeLimit($this->rangeLimit)->setPage($this->page); $this->iterator = $paginator->getIterator(); $this->paginator = $paginator; } else { $items = $this->queryBuilder->getQuery()->getResult(); $this->iterator = new \ArrayIterator($items); $this->paginator = null; } $this->initialized = true; }
public function implement(QueryBuilder $queryBuilder) { if ($this->payload->get('count')->getValue() === true) { return $queryBuilder; } $i = 0; foreach ($this->getValue() as $field => $direction) { if ($i === 0) { $queryBuilder->orderBy($field, $direction); } else { $queryBuilder->addOrderBy($field, $direction); } ++$i; } }
/** * Order data * @param array $sorting * @return self */ public function sort(array $sorting) { $alias = current($this->data_source->getDQLPart('from'))->getAlias(); if ($sorting) { foreach ($sorting as $column => $sort) { $this->data_source->orderBy("{$alias}.{$column}", $sort); } } else { /** * Has the statement already a order by clause? */ if (!$this->data_source->getDQLPart('orderBy')) { $this->data_source->orderBy("{$alias}.{$this->primary_key}"); } } return $this; }
/** * Maps the specified criteria to the query builder * @param QueryBuilder $qb The query builder * @param mixed[string] $criteria The search criteria * @param mixed[string] $ordering The result ordering * @return \Doctrine\ORM\QueryBuilder * @throws */ protected function mapCriteriaToBuilder(QueryBuilder $qb, array $criteria = [], array $ordering = []) { $method = 'orWhere'; //get the metadata $metadata = $this->getClassMetadata(); foreach ($criteria as $propertyName => $property) { if (is_array($property)) { $propertyValue = $property['value']; $comparator = $property['operator']; } else { $propertyValue = $property; $comparator = 'eq'; } //check the property exists on the entity (otherwise the query may contain injected DQL) if (!$metadata->hasField($propertyName) && !$metadata->hasAssociation($propertyName)) { throw new \InvalidArgumentException("Invalid property \"{$propertyName}\"."); } //add the criteria if ($comparator === 'eq') { $qb->{$method}($qb->expr()->eq('e.' . $propertyName, $qb->expr()->literal($propertyValue))); } elseif ($comparator === 'neq') { $qb->{$method}($qb->expr()->neq('e.' . $propertyName, $qb->expr()->literal($propertyValue))); } elseif ($method === 'lt') { $qb->{$method}($qb->expr()->lt('e.' . $propertyName, $qb->expr()->literal($propertyValue))); } elseif ($comparator === 'lte') { $qb->{$method}($qb->expr()->lte('e.' . $propertyName, $qb->expr()->literal($propertyValue))); } elseif ($comparator === 'gt') { $qb->{$method}($qb->expr()->gt('e.' . $propertyName, $qb->expr()->literal($propertyValue))); } elseif ($comparator === 'gte') { $qb->{$method}($qb->expr()->gte('e.' . $propertyName, $qb->expr()->literal($propertyValue))); } elseif ($comparator === 'like') { $qb->{$method}($qb->expr()->like('e.' . $propertyName, $qb->expr()->literal($propertyValue))); } elseif ($comparator === 'notlike') { $qb->{$method}($qb->expr()->notlike('e.' . $propertyName, $qb->expr()->literal($propertyValue))); } } //order by each property foreach ($ordering as $propertyName => $propertyValue) { $qb->orderBy('e.' . $propertyName, $propertyValue); } return $qb; }
/** * Sort data * @param Sorting $sorting * @return static */ public function sort(Sorting $sorting) { if (is_callable($sorting->getSortCallback())) { call_user_func($sorting->getSortCallback(), $this->data_source, $sorting->getSort()); return $this; } $sort = $sorting->getSort(); if (!empty($sort)) { foreach ($sort as $column => $order) { $this->data_source->addOrderBy($this->checkAliases($column), $order); } } else { /** * Has the statement already a order by clause? */ if (!$this->data_source->getDQLPart('orderBy')) { $this->data_source->orderBy($this->checkAliases($this->primary_key)); } } return $this; }
public function buildSelectQuery(QueryBuilder $queryBuilder, $parameters, $class, $sort = array(), $join = "", $joinParams = array()) { if (!empty($joinParams)) { foreach ($joinParams as $key => $joinParam) { unset($parameters[$key]); } } if (!empty($sort)) { unset($parameters[$sort['key']]); } $from = "DraftBundle:" . $class; $queryBuilder->select("c")->from($from, "c"); if (isset($parameters['limit'])) { $limit = $parameters['limit']; $queryBuilder->setMaxResults($limit); if (isset($parameters['page'])) { $page = ($parameters['page'] - 1) * $limit; $queryBuilder->setFirstResult($page); unset($parameters['page']); } unset($parameters['limit']); } $i = 0; foreach ($parameters as $key => $value) { if ($value === "") { continue; } if ($i == 0) { $queryBuilder->where("c.{$key} = :param{$i}")->setParameter(":param{$i}", "{$value}"); } else { $queryBuilder->andWhere("c.{$key} = :param{$i}")->setParameter(":param{$i}", "{$value}"); } $i++; } if (!empty($sort)) { $queryBuilder->orderBy("c." . $sort['key'], $sort['value']); } return $queryBuilder; }
private function queryOrderField(QueryBuilder $qb, $field, $sens = 'ASC') { $this->logger->debug('VALUE field: ' . $field); switch ($field) { case 'duration': $fieldName = 'v.duration'; break; case 'title': $fieldName = 'mov.title'; break; case 'create': $fieldName = 'v.createDate'; break; default: $fieldName = null; } $this->logger->debug('VALUE fieldName: ' . $fieldName); if (!is_null($fieldName)) { $qb->orderBy($fieldName, $sens); } return $qb; }
/** * {@inheritdoc} * * @return array Array of hydrated elements. Empty if reached the end of collection or collection is empty. * * @throws \UnexpectedValueException Thrown if iterator incorrectly initialized. */ public function next() { if (\is_null($this->qb)) { if (null === $this->originalQb) { throw new \UnexpectedValueException("Query Builder object not specified."); } if (null === $this->iterateBy) { throw new \UnexpectedValueException("IterateBy value not specified."); } $this->qb = clone $this->originalQb; $this->qb->orderBy($this->iterateBy); $this->qb->setMaxResults($this->pageSize); } $qb = clone $this->qb; if ($this->currentPage++ > 0) { $qb->andWhere($this->qb->expr()->gt($this->iterateBy, $this->lastValue)); } $result = $qb->getQuery()->getResult($this->hydrationMode); if (\count($result)) { $method = $this->pullClosure; $this->lastValue = $method($result[\count($result) - 1]); } return $result; }
/** * @param QueryBuilder $qb * @param string $name */ protected function addQueryBuilderSort(QueryBuilder $qb, $name) { $alias = current($qb->getDQLPart('from'))->getAlias(); if (is_array($order = $this->getOrder($name))) { $qb->orderBy($alias . '.' . $order['field'], $order['type']); } }
/** * Builds the query for the given data request object * * @access public * @param \Zepi\DataSource\Core\Entity\DataRequest $dataRequest * @param \Doctrine\ORM\QueryBuilder $queryBuilder * @param string $entity * @param string $tableCode */ public function buildDataRequestQuery(DataRequest $dataRequest, QueryBuilder $queryBuilder, $entity, $tableCode) { $queryBuilder->select($tableCode)->from($entity, $tableCode); $hasWhere = false; $i = 1; foreach ($dataRequest->getFilters() as $filter) { $whereQuery = $tableCode . '.' . $filter->getFieldName() . ' ' . $filter->getMode() . ' :' . $i; if ($hasWhere) { $queryBuilder->andWhere($whereQuery); } else { $queryBuilder->where($whereQuery); $hasWhere = true; } $queryBuilder->setParameter($i, $filter->getNeededValue()); $i++; } // Sorting if ($dataRequest->hasSorting()) { $mode = 'ASC'; if (in_array($dataRequest->getSortByDirection(), array('ASC', 'DESC'))) { $mode = $dataRequest->getSortByDirection(); } $queryBuilder->orderBy($tableCode . '.' . $dataRequest->getSortBy(), $mode); } // Offset if ($dataRequest->hasRange()) { $queryBuilder->setFirstResult($dataRequest->getOffset()); $queryBuilder->setMaxResults($dataRequest->getNumberOfEntries()); } }
/** * {@inheritDoc} */ public function execute() { $this->queryBuilder = $this->createQueryBuilder(); foreach ($this->fields as $field) { $this->queryBuilder->addSelect($field->getSelect() . ' AS ' . $field->getName()); } if ($this->limit != null) { $this->queryBuilder->setMaxResults($this->limit)->setFirstResult($this->limit * ($this->page - 1)); } if ($this->sortField != null) { $this->queryBuilder->orderBy($this->sortField->getSelect(), $this->sortOrder); } return $this->queryBuilder->getQuery()->getArrayResult(); }