PHP QueryBuilder::addOrderBy方法代码示例

 /**
  * @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]);
 }

 /**
  * @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, $locale = null, $scope = null)
 {
     $this->qb->addOrderBy($field, $direction);
     $idField = $this->qb->getRootAlias() . '.id';
     $this->qb->addOrderBy($idField);
     return $this;
 }

 /**
  * {@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;
 }

 /**
  * {@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;
 }

 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)
 {
     $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;
 }

 /**
  * 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);
     }
 }

 /**
  * {@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);
     }
 }

 /**
  * @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;
 }