/** * @param QueryBuilder $queryBuilder * @param Parameter $parameters * @return QueryBuilder */ protected function generateQueryParamFilter(QueryBuilder $queryBuilder, Parameters $parameters) { if (isset($parameters['query']) && is_array($parameters['query'])) { $i = 1; foreach ($parameters['query'] as $query) { $type = '='; if ($query['type'] == 'like') { $type = 'LIKE'; } $whereString = sprintf('row.%s %s :%sValue%d', $query['field'], $type, $query['field'], $i); if (!isset($query['where']) || $query['where'] == 'and') { $queryBuilder->andWhere($whereString); } else { if ($query['where'] == 'or') { $queryBuilder->orWhere($whereString); } } $queryBuilder->setParameter(sprintf('%sValue%d', $query['field'], $i), $query['value']); $i++; } } if (isset($parameters['orderBy']) && is_array($parameters['orderBy'])) { foreach ($parameters['orderBy'] as $orderKey => $orderDirection) { $queryBuilder->addOrderBy('row.' . $orderKey, strtoupper($orderDirection)); } } return $queryBuilder; }
/** * @dataProvider orderByProvider */ public function testAddOrderBy($alias, $orderBy, $expectedOrderBy) { foreach ($expectedOrderBy as $field => $order) { $this->queryBuilder->addOrderBy($field, $order)->shouldBeCalledTimes(1); } $this->addOrderByFunction->invokeArgs($this->orderByTrait, [$this->queryBuilder->reveal(), $alias, $orderBy]); }
/** * {@inheritdoc} */ public function addFieldSorter($field, $direction, $locale = null, $scope = null) { $this->qb->addOrderBy($field, $direction); $idField = $this->qb->getRootAlias() . '.id'; $this->qb->addOrderBy($idField); return $this; }
/** * @param Request $request * @param QueryBuilder $queryBuilder * * @return QueryBuilder */ public function execute(Request $request, QueryBuilder $queryBuilder) { $filteredProperties = array(); $sort = $request->get('sort'); if (!is_null($sort) && !is_array($sort)) { $elements = explode(',', $sort, 99); foreach ($elements as $element) { if ($element) { $sort = 'asc'; if (substr($element, 0, 1) == '+') { $element = substr($element, 1); } else { if (substr($element, 0, 1) == '-') { $element = substr($element, 1); $sort = 'desc'; } } if (array_key_exists($element, $this->orderProperties)) { $filteredProperties[] = $element; $queryBuilder->addOrderBy($this->orderProperties[$element][0], $sort); } } } } foreach ($this->orderProperties as $property => $value) { if (!array_key_exists($property, $filteredProperties)) { if (!is_null($value[1])) { $queryBuilder->addOrderBy($value[0], $value[1]); } } } return $queryBuilder; }
/** * {@inheritdoc} */ public function addFieldSorter($field, $direction) { $alias = 'sorterCompleteness'; $util = new CompletenessJoin($this->qb); $util->addJoins($alias); $this->qb->addOrderBy($alias . '.ratio', $direction); return $this; }
/** * {@inheritdoc} */ public function addFieldSorter($field, $direction, $locale = null, $scope = null) { $alias = 'inGroupSorter'; $inGroupExpr = 'CASE WHEN :currentGroup MEMBER OF p.groups THEN true ELSE false END'; $this->qb->addSelect(sprintf('%s AS %s', $inGroupExpr, $alias))->addOrderBy($alias, $direction); $idField = $this->qb->getRootAlias() . '.id'; $this->qb->addOrderBy($idField); return $this; }
protected function genericBaseQueryAddOrderBy(QueryBuilder $qb, $orderBy = '') { if ($orderBy == 'sort asc') { $qb->addOrderBy('tbl.group', 'asc'); $qb->addOrderBy('tbl.sort', 'asc'); return $qb; } else { return parent::genericBaseQueryAddOrderBy($qb, $orderBy); } }
/** * {@inheritdoc} */ public function addFieldSorter($field, $direction, $locale = null, $scope = null) { $alias = 'sorterCompleteness'; $util = new CompletenessJoin($this->qb); $util->addJoins($alias, $locale, $scope); $this->qb->addOrderBy($alias . '.ratio', $direction); $idField = $this->qb->getRootAlias() . '.id'; $this->qb->addOrderBy($idField); return $this; }
/** * {@inheritdoc} */ public function addFieldSorter($field, $direction) { $rootAlias = $this->qb->getRootAlias(); $prefix = 'sorter'; $field = $prefix . 'familyLabel'; $family = $prefix . 'family'; $trans = $prefix . 'familyTranslations'; $this->qb->leftJoin($rootAlias . '.family', $family)->leftJoin($family . '.translations', $trans, 'WITH', $trans . '.locale = :dataLocale'); $this->qb->addSelect('COALESCE(' . $trans . '.label, CONCAT(\'[\', ' . $family . '.code, \']\')) as ' . $field); $this->qb->addOrderBy($field, $direction); return $this; }
/** * {@inheritdoc} */ public function addAttributeSorter(AttributeInterface $attribute, $direction, $locale = null, $scope = null) { $aliasPrefix = 'sorter'; $joinAlias = $aliasPrefix . 'V' . $attribute->getCode(); $backendType = $attribute->getBackendType(); // join to value $condition = $this->prepareAttributeJoinCondition($attribute, $joinAlias, $locale, $scope); $this->qb->leftJoin($this->qb->getRootAlias() . '.values', $joinAlias, 'WITH', $condition); $joinAliasMetric = $aliasPrefix . 'M' . $attribute->getCode(); $this->qb->leftJoin($joinAlias . '.' . $backendType, $joinAliasMetric); $this->qb->addOrderBy($joinAliasMetric . '.baseData', $direction); $idField = $this->qb->getRootAlias() . '.id'; $this->qb->addOrderBy($idField); return $this; }
/** * Adds conditions to the query builder related to the specification. The * specification should add parameters as required and return the expression to be * added to the QueryBuilder. * * @param \Doctrine\ORM\QueryBuilder $qb * @param string $dqlAlias * @return \Doctrine\ORM\Query\Expr */ public function match(QueryBuilder $qb, $dqlAlias) { foreach ($this->sortFields as $field => $direction) { $qb->addOrderBy(sprintf('%s.%s', $dqlAlias, $field), $direction); } return parent::match($qb, $dqlAlias); }
/** * 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; }
/** * 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; }
/** * @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 (array_key_exists('alerts', $criteria)) { $ids = is_array($criteria['alerts']) ? $criteria['alerts'] : [$criteria['alerts']]; $qb->join('x.alerts', 'al'); $qb->andWhere($qb->expr()->in('al.id', ':alerts')); $qb->setParameter(':alerts', $ids); } //cleanup all the possible relationship filters unset($criteria['alerts']); if (count($criteria)) { foreach ($criteria as $key => $value) { $values = is_array($value) ? $value : [$value]; $qb->andWhere($qb->expr()->in("x.{$key}", ":{$key}")); $qb->setParameter(":{$key}", $values); } } if (empty($orderBy)) { $orderBy = ['id' => 'ASC']; } if (is_array($orderBy)) { foreach ($orderBy as $sort => $order) { $qb->addOrderBy('x.' . $sort, $order); } } if ($offset) { $qb->setFirstResult($offset); } if ($limit) { $qb->setMaxResults($limit); } return $qb; }
/** * @param QueryBuilder $qb * @param string $dqlAlias */ public function modify(QueryBuilder $qb, $dqlAlias) { if ($this->dqlAlias !== null) { $dqlAlias = $this->dqlAlias; } $qb->addOrderBy(sprintf('%s.%s', $dqlAlias, $this->field), $this->order); }
/** * {@inheritdoc} * * Orders collection by properties. The order of the ordered properties is the same as the order specified in the * query. * For each property passed, if the resource does not have such property or if the order value is different from * `asc` or `desc` (case insensitive), the property is ignored. */ public function apply(ResourceInterface $resource, QueryBuilder $queryBuilder) { $request = $this->requestStack->getCurrentRequest(); if (null === $request) { return; } $properties = $this->extractProperties($request); foreach ($properties as $property => $order) { if (!$this->isPropertyEnabled($property) || !$this->isPropertyMapped($property, $resource)) { continue; } if (empty($order) && isset($this->properties[$property])) { $order = $this->properties[$property]; } $order = strtoupper($order); if (!in_array($order, ['ASC', 'DESC'])) { continue; } $alias = 'o'; $field = $property; if ($this->isPropertyNested($property)) { $propertyParts = $this->splitPropertyParts($property); $parentAlias = $alias; foreach ($propertyParts['associations'] as $association) { $alias = QueryNameGenerator::generateJoinAlias($association); $queryBuilder->leftJoin(sprintf('%s.%s', $parentAlias, $association), $alias); $parentAlias = $alias; } $field = $propertyParts['field']; } $queryBuilder->addOrderBy(sprintf('%s.%s', $alias, $field), $order); } }
/** * @return void */ private function setOrderBy() { $List = $this->getTableOrderBy(); foreach ((array) $List as $Literal) { $this->QueryBuilder->addOrderBy($Literal); } }
/** * Add order rules to the query builder * NB! The alias of the main table must be "e". * @param QueryBuilder $qb * @throws Exception\InvalidArgument */ public function applyToQueryBuilder(QueryBuilder $qb, &$parameterOffset = 0) { $orderRules = $this->orderRules; foreach ($orderRules as $orderRule) { $field = $orderRule[self::FIELD_POS]; switch ($field) { case self::LEFT_FIELD: case self::RIGHT_FIELD: case self::LEVEL_FIELD: break; default: throw new Exception\InvalidArgument("Field {$field} is not recognized"); } $field = "e.{$field}"; $directionSQL = null; $direction = $orderRule[self::DIRECTION_POS]; switch ($direction) { case self::DIRECTION_ASCENDING: $directionSQL = 'ASC'; break; case self::DIRECTION_DESCENDING: $directionSQL = 'DESC'; break; default: throw new Exception\InvalidArgument("Direction {$direction} is not recognized"); } $qb->addOrderBy($field, $directionSQL); } }
/** * Sets sorting. * @param array $sorting */ public function sort(array $sorting) { foreach ($sorting as $key => $value) { $column = isset($this->sortMapping[$key]) ? $this->sortMapping[$key] : current($this->qb->getRootAliases()) . '.' . $key; $this->qb->addOrderBy($column, $value); } }
/** * @param QueryBuilder $queryBuilder * @param array $sorting */ protected function applySorting(QueryBuilder $queryBuilder, array $sorting = []) { foreach ($sorting as $property => $order) { if (!empty($order)) { $queryBuilder->addOrderBy($this->getPropertyName($property), $order); } } }
/** * @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(); }
protected function orderBy($columnName, $direction) { if ($columnName === 'edit') { $this->queryBuilder->addOrderBy($this->getEntityName() . '.' . 'id', $direction); return; } $this->queryBuilder->addOrderBy($this->parseToQueryBuilderFormat($columnName), $direction); }
/** * {@inheritdoc} */ public function applyToCollection(ResourceInterface $resource, QueryBuilder $queryBuilder) { $classMetaData = $queryBuilder->getEntityManager()->getClassMetadata($resource->getEntityClass()); $identifiers = $classMetaData->getIdentifier(); if (null !== $this->order && 1 === count($identifiers)) { $identifier = $identifiers[0]; $queryBuilder->addOrderBy('o.' . $identifier, $this->order); } }
/** * {@inheritdoc} */ public function addAttributeSorter(AttributeInterface $attribute, $direction, $locale = null, $scope = null) { if (null === $locale) { throw new \InvalidArgumentException(sprintf('Cannot prepare condition on type "%s" without locale', $attribute->getAttributeType())); } $aliasPrefix = 'sorter'; // join to values $joinAlias = $aliasPrefix . 'V' . $attribute->getCode(); $condition = $this->prepareAttributeJoinCondition($attribute, $joinAlias, $locale, $scope); $this->qb->leftJoin(current($this->qb->getRootAliases()) . '.values', $joinAlias, 'WITH', $condition); // join to reference data $joinAliasOpt = $attribute->getCode(); $this->qb->leftJoin($joinAlias . '.' . $attribute->getReferenceDataName(), $joinAliasOpt); $this->qb->addOrderBy($joinAliasOpt . '.code', $direction); $idField = current($this->qb->getRootAliases()) . '.id'; $this->qb->addOrderBy($idField); return $this; }
public function orderBy(QueryBuilder $queryBuilder, $metadata, $option) { if (!isset($option['alias'])) { $option['alias'] = 'row'; } if (!isset($option['direction']) || !in_array(strtolower($option['direction']), array('asc', 'desc'))) { throw new Exception('Invalid direction in Field OrderBy'); } $queryBuilder->addOrderBy($option['alias'] . '.' . $option['field'], $option['direction']); }
/** * {@inheritdoc} */ public function applyToCollection(QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, string $operationName = null) { $classMetaData = $queryBuilder->getEntityManager()->getClassMetadata($resourceClass); $identifiers = $classMetaData->getIdentifier(); if (null !== $this->order) { foreach ($identifiers as $identifier) { $queryBuilder->addOrderBy('o.' . $identifier, $this->order); } } }
/** * Function adds order-by to querybuilder based on $sortBy-data given. * * @param QueryBuilder $queryBuilder * @param array $sortBy */ protected function addOrderBy(QueryBuilder $queryBuilder, $alias, array $sortBy = []) { foreach ($sortBy as $field => $order) { // if no relation is defined add alias by default if (strpos($field, '.') === false) { $field = $alias . '.' . $field; } $queryBuilder->addOrderBy($field, $order); } }
/** * 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; }
/** * Manages the order by statement into queries. * * @param \Doctrine\ORM\QueryBuilder $queryBuilder The query builder * @param string[] $sorting Array which contains the sorting as key value, * if order is empty, the default value is DESC * * @return \Doctrine\ORM\QueryBuilder */ public function orderBy(QueryBuilder $queryBuilder, array $sorting = []) { foreach ($sorting as $property => $order) { if (!(array_keys($sorting) !== range(0, count($sorting) - 1))) { $property = $order; $order = 'DESC'; } $queryBuilder->addOrderBy($this->getPropertyName($property), $order); } return $queryBuilder; }
/** * 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; }