PHP QueryBuilder::addOrderBy方法代码示例

 /**
  * @internal
  */
 private function incorporateDbalQueryBuilder(QueryBuilder $qb, FilterInterface $filter)
 {
     $criteria = $this->getFilteringCriteria($filter);
     // extraindo os rootAliases, pois o DBAL\QueryBuilder não tem
     $fromPart = $qb->getQueryPart('from');
     $rootAliases = array();
     foreach ($fromPart as $part) {
         $rootAliases[] = $part['alias'];
     }
     $visitor = new DbalQueryExpressionVisitor($qb->getConnection(), $rootAliases, $this->fieldMap);
     if ($whereExpression = $criteria->getWhereExpression()) {
         $qb->andWhere($visitor->dispatch($whereExpression));
         $qb->setParameters($visitor->getParameters());
     }
     if ($criteria->getOrderings()) {
         foreach ($criteria->getOrderings() as $sort => $order) {
             $qb->addOrderBy($visitor->getFieldName($sort), $order);
         }
     }
     if (($firstResult = $criteria->getFirstResult()) !== null) {
         $qb->setFirstResult($firstResult);
     }
     if (($maxResults = $criteria->getMaxResults()) !== null) {
         $qb->setMaxResults($maxResults);
     }
 }

 /**
  * Build the data query
  *
  * @return QueryBuilder
  */
 public function getDataQuery()
 {
     $this->buildFilteredQuery();
     if ($this->offset !== null) {
         $this->currentQueryBuilder->setFirstResult($this->offset);
     }
     if ($this->length !== null) {
         $this->currentQueryBuilder->setMaxResults($this->length);
     }
     if ($this->orders !== null) {
         foreach ($this->orders as $order) {
             list($column, $dir) = $order;
             $this->currentQueryBuilder->addOrderBy($column->sourceSort, $dir);
         }
     }
     if (isset($this->conf->group)) {
         $this->currentQueryBuilder->groupBy($this->conf->group);
     }
     return $this->currentQueryBuilder;
 }

 /**
  * @inheritdoc
  */
 protected function setOrderBy(ColumnPriorityQueue $gridColumns, QueryBuilder $queryBuilder)
 {
     $queryBuilder->addOrderBy('main.start', 'DESC')->addOrderBy('main.end', 'DESC')->addOrderBy('main.id', 'DESC');
 }

 /**
  * @param \ACP3\Core\Helpers\DataGrid\ColumnPriorityQueue $gridColumns
  * @param \Doctrine\DBAL\Query\QueryBuilder               $queryBuilder
  */
 protected function setOrderBy(ColumnPriorityQueue $gridColumns, QueryBuilder $queryBuilder)
 {
     foreach (clone $gridColumns as $gridColumn) {
         if ($gridColumn['default_sort'] === true) {
             if (!is_array($gridColumn['fields'])) {
                 $gridColumn['fields'] = [$gridColumn['fields']];
             }
             foreach ($gridColumn['fields'] as $field) {
                 $queryBuilder->addOrderBy($field, strtoupper($gridColumn['default_sort_direction']));
             }
         }
     }
 }

 /**
  * @param array $request
  * @return array
  */
 public function make(array $request)
 {
     $output = ['data' => [], 'draw' => $request['draw'], 'recordsFiltered' => 0, 'recordsTotal' => 0];
     /**
      * Order By
      */
     if (isset($request['order'])) {
         for ($i = 0; $i < count($request['order']); ++$i) {
             $j = intval($request['order'][$i]['column']);
             if ($request['columns'][$j]['orderable'] != 'true') {
                 continue;
             }
             $column = $request['columns'][$j]['data'];
             $sort = $request['order'][$i]['dir'];
             $this->query->addOrderBy($column, $sort);
         }
     }
     /**
      * Count All
      */
     $temp = clone $this->query;
     $temp->resetQueryPart('select');
     $temp->resetQueryPart('orderBy');
     $temp->select("COUNT(*)");
     $output['recordsTotal'] = $temp->execute()->fetchColumn(0);
     /**
      * Filter
      */
     for ($i = 0; $i < count($request['columns']); ++$i) {
         if ($request['columns'][$i]['searchable'] != 'true') {
             continue;
         }
         $value = $request['columns'][$i]['search']['value'];
         if (strlen($value) > 0) {
             $column = $request['columns'][$i]['data'];
             $value = $this->query->getConnection()->quote("{$value}%");
             $this->query->andHaving($this->query->expr()->like($column, $value));
         }
     }
     /**
      * Search
      */
     if (isset($request['search'])) {
         $value = $request['search']['value'];
         if (strlen($value) > 0) {
             for ($i = 0; $i < count($request['columns']); ++$i) {
                 if ($request['columns'][$i]['searchable'] != 'true') {
                     continue;
                 }
                 $column = $request['columns'][$i]['data'];
                 $this->query->orHaving($this->query->expr()->like($column, ':search'));
             }
             $this->query->setParameter('search', "%{$value}%");
         }
     }
     /**
      * Count Filtered
      */
     $temp = clone $this->query;
     $temp->resetQueryPart('orderBy');
     $output['recordsFiltered'] = $temp->execute()->rowCount();
     /**
      * Limit
      */
     if (isset($request['start'])) {
         $this->query->setFirstResult($request['start']);
     }
     if (isset($request['length'])) {
         $this->query->setMaxResults($request['length']);
     }
     /**
      * Fetch Results
      */
     $output['data'] = $this->query->execute()->fetchAll(\PDO::FETCH_ASSOC);
     /**
      * Add Filter
      */
     return $output;
 }

 /**
  * @test
  */
 public function addOrderByQuotesIdentifierAndDelegatesToConcreteQueryBuilder()
 {
     $this->connection->quoteIdentifier('aField')->shouldBeCalled()->willReturnArgument(0);
     $this->concreteQueryBuilder->addOrderBy('aField', 'DESC')->shouldBeCalled()->willReturn($this->subject);
     $this->subject->addOrderBy('aField', 'DESC');
 }

 /**
  * {@inheritdoc}
  */
 public function addOrderBy($field, $direction)
 {
     return $this->queryBuilder->addOrderBy($field, $direction);
 }

 /**
  * Adds an ordering to the query results.
  *
  * @param string $sort The ordering expression.
  * @param string $order The ordering direction.
  *
  * @return \OCP\DB\QueryBuilder\IQueryBuilder This QueryBuilder instance.
  */
 public function addOrderBy($sort, $order = null)
 {
     $this->queryBuilder->addOrderBy($this->helper->quoteColumnName($sort), $order);
     return $this;
 }

 /**
  * Builds the ORDER BY part
  * @param QueryBuilder $qb
  * @param array $order_by
  * @return self
  */
 protected function buildOrderBy(QueryBuilder $qb, array $order_by = [])
 {
     foreach ($order_by as $order) {
         $column = null;
         $direction = 'ASC';
         if (is_string($order)) {
             $column = $order;
         } elseif (is_array($order) && count($order) === 2) {
             list($column, $direction) = $order;
         }
         if ($column === null || $this->getColumn($column) === null) {
             throw Schema\SchemaException::columnDoesNotExist($column, $this->table_name);
         }
         if (!in_array($direction, ['ASC', 'DESC'])) {
             throw QueryBuilderException::orderByDirectionDoesNotExist($direction);
         }
         $qb->addOrderBy($this->conn->quoteIdentifier($column), $direction);
     }
     return $this;
 }

 /**
  * Adds an ordering to the query results.
  *
  * @param string $sort The ordering expression.
  * @param string $order The ordering direction.
  * @return QueryBuilder This QueryBuilder instance.
  */
 public function addOrderBy($sort, $order = null)
 {
     $connection = $this->getConnection();
     return parent::addOrderBy($connection->quoteIdentifier($sort), $order);
 }

 /**
  * Helper function which iterates all sort arrays and at them as order by condition.
  * @param DBALQueryBuilder $builder
  * @param $sort
  * @return $this
  */
 private function addSort(DBALQueryBuilder $builder, $sort)
 {
     if (empty($sort)) {
         return $this;
     }
     foreach ($sort as $condition) {
         $builder->addOrderBy($condition['property'], $condition['direction']);
     }
     return $this;
 }

 /**
  * Sort products for current category by passed sort type
  *
  * @param QueryBuilder $builder
  * @param integer $orderBy
  */
 private function sortUnsortedByDefault($builder, $orderBy)
 {
     switch ($orderBy) {
         case 1:
             $builder->addOrderBy('product.datum', 'DESC')->addOrderBy('product.changetime', 'DESC');
             break;
         case 2:
             $builder->leftJoin('product', 's_articles_top_seller_ro', 'topSeller', 'topSeller.article_id = product.id')->addOrderBy('topSeller.sales', 'DESC')->addOrderBy('topSeller.article_id', 'DESC');
             break;
         case 3:
             $builder->addSelect('MIN(ROUND(defaultPrice.price * priceVariant.minpurchase * 1, 2)) as cheapest_price')->leftJoin('product', 's_articles_prices', 'defaultPrice', 'defaultPrice.articleID = product.id')->innerJoin('defaultPrice', 's_articles_details', 'priceVariant', 'priceVariant.id = defaultPrice.articledetailsID')->addOrderBy('cheapest_price', 'ASC')->addOrderBy('product.id', 'DESC');
             break;
         case 4:
             $builder->addSelect('MIN(ROUND(defaultPrice.price * priceVariant.minpurchase * 1, 2)) as cheapest_price')->leftJoin('product', 's_articles_prices', 'defaultPrice', 'defaultPrice.articleID = product.id')->innerJoin('defaultPrice', 's_articles_details', 'priceVariant', 'priceVariant.id = defaultPrice.articledetailsID')->addOrderBy('cheapest_price', 'DESC')->addOrderBy('product.id', 'DESC');
             break;
         case 5:
             $builder->addOrderBy('product.name', 'ASC');
             break;
         case 6:
             $builder->addOrderBy('product.name', 'DESC');
             break;
         case 7:
             $builder->addSelect('(SUM(vote.points) / COUNT(vote.id)) as votes')->leftJoin('product', 's_articles_vote', 'vote', 'product.id = vote.articleID')->addOrderBy('votes', 'DESC')->addOrderBy('product.id', 'DESC')->groupBy('product.id');
             break;
         case 9:
             $builder->innerJoin('product', 's_articles_details', 'variant', 'variant.id = product.main_detail_id')->addOrderBy('variant.instock', 'ASC')->addOrderBy('product.id', 'DESC');
             break;
         case 10:
             $builder->innerJoin('product', 's_articles_details', 'variant', 'variant.id = product.main_detail_id')->addOrderBy('variant.instock', 'DESC')->addOrderBy('product.id', 'DESC');
             break;
     }
 }

 /**
  * Adds an ordering to the query results.
  *
  * @param string $sort  The ordering expression.
  * @param string $order The ordering direction.
  *
  * @return self
  */
 public function addOrderBy($sort, $order = null)
 {
     $this->qb->addOrderBy($sort, $order);
     return $this;
 }

 public function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query)
 {
     $paramcount = 0;
     if (!empty($this->gIDs)) {
         $validgids = array();
         foreach ($this->gIDs as $gID) {
             if ($gID > 0) {
                 $validgids[] = $gID;
             }
         }
         if (!empty($validgids)) {
             $query->innerJoin('p', 'VividStoreProductGroups', 'g', 'p.pID = g.pID and g.gID in (' . implode(',', $validgids) . ')');
             if (!$this->groupMatchAny) {
                 $query->having('count(g.gID) = ' . count($validgids));
             }
         }
     }
     switch ($this->sortBy) {
         case "alpha":
             $query->orderBy('pName', 'ASC');
             break;
         case "date":
             $query->orderBy('pDateAdded', 'DESC');
             break;
         case "pricelth":
             $query->orderBy('pPrice', 'ASC');
             break;
         case "pricehtl":
             $query->orderBy('pPrice', 'DESC');
             break;
         case "popular":
             $pr = new StoreProductReport();
             $pr->sortByPopularity();
             $products = $pr->getProducts();
             $pIDs = array();
             foreach ($products as $product) {
                 $pIDs[] = $product['pID'];
             }
             foreach ($pIDs as $pID) {
                 $query->addOrderBy("pID = ?", 'DESC')->setParameter($paramcount++, $pID);
             }
             break;
     }
     switch ($this->featured) {
         case "featured":
             $query->andWhere("pFeatured = 1");
             break;
         case "nonfeatured":
             $query->andWhere("pFeatured = 0");
             break;
     }
     if (!$this->showOutOfStock) {
         $query->andWhere("pQty > 0 OR pQtyUnlim = 1");
     }
     if ($this->activeOnly) {
         $query->andWhere("pActive = 1");
     }
     if (is_array($this->cIDs) && !empty($this->cIDs)) {
         $query->innerJoin('p', 'VividStoreProductLocations', 'l', 'p.pID = l.pID and l.cID in (' . implode(',', $this->cIDs) . ')');
     }
     $query->groupBy('p.pID');
     if ($this->search) {
         $query->andWhere('pName like ?')->setParameter($paramcount++, '%' . $this->search . '%');
     }
     return $query;
 }

 /**
  * @inheritdoc
  */
 public function applySorting(QueryBuilder $builder, $modelClass, array $sortParams)
 {
     $table = $this->getTableName($modelClass);
     foreach ($sortParams as $sortParam) {
         /** @var SortParameterInterface $sortParam */
         $column = null;
         if ($sortParam->isRelationship() === false) {
             $column = $sortParam->getName();
         } elseif ($sortParam->getRelationshipType() === RelationshipTypes::BELONGS_TO) {
             $column = $this->getModelSchemes()->getForeignKey($modelClass, $sortParam->getName());
         }
         if ($column !== null) {
             $builder->addOrderBy($this->buildTableColumnName($table, $column), $sortParam->isAscending() === true ? 'ASC' : 'DESC');
         }
     }
 }