PHP QueryBuilder::orderBy方法代码示例

 public function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query)
 {
     $paramcount = 0;
     if (isset($this->gID) && $this->gID > 0) {
         $query->where('gID = ?')->setParameter($paramcount++, $this->gID);
     }
     switch ($this->sortBy) {
         case "alpha":
             $query->orderBy('pName', 'ASC');
             break;
         case "date":
             $query->orderBy('pDateAdded', 'DESC');
             break;
     }
     switch ($this->featured) {
         case "featured":
             $query->andWhere("pFeatured = 1");
             break;
         case "nonfeatured":
             $query->andWhere("pFeatured = 0");
             break;
     }
     if ($this->activeOnly) {
         $query->andWhere("pActive = 1");
     }
     if ($this->search) {
         $query->andWhere('pName like ?')->setParameter($paramcount++, '%' . $this->search . '%');
     }
     return $query;
 }

 protected function executeSortBy($column, $direction = 'asc')
 {
     if (in_array(strtolower($direction), array('asc', 'desc'))) {
         $this->query->orderBy($column, $direction);
     } else {
         throw new \Exception(t('Invalid SQL in order by'));
     }
 }

 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 "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;
 }

 /**
  * Applies operation to data source and returns modified data source.
  *
  * @param QueryBuilder $src
  * @param OperationInterface|SortOperation $operation
  * @return QueryBuilder
  */
 public function process($src, OperationInterface $operation)
 {
     $field = $operation->getField();
     $order = $operation->getOrder();
     $src->orderBy($field, $order);
     return $src;
 }

 /**
  * @param QueryBuilder  $queryBuilder
  * @param SortInterface $sort
  * @param Mapping       $mapping
  */
 public static function sort(QueryBuilder $queryBuilder, SortInterface $sort, Mapping $mapping)
 {
     $columns = $mapping->map();
     foreach ($sort->orders() as $propertyName => $order) {
         self::guardColumnExists($columns, $propertyName);
         $queryBuilder->orderBy($columns[$propertyName], $order->isAscending() ? Order::ASCENDING : Order::DESCENDING);
     }
 }

 public function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query)
 {
     $paramcount = 0;
     if (isset($this->gID) && $this->gID > 0) {
         $query->where('gID = ?')->setParameter($paramcount++, $this->gID);
     }
     switch ($this->sortBy) {
         case "alpha":
             $query->orderBy('drName', 'ASC');
             break;
     }
     if ($this->search) {
         $query->andWhere('drName like ?')->setParameter($paramcount++, '%' . $this->search . '%');
     }
     $query->andWhere('drDeleted is NULL');
     return $query;
 }

    /**
     * @see \ComPHPPuebla\Doctrine\TableGateway\Specification\QueryBuilderSpecification::match()
     */
    public function match(QueryBuilder $qb)
    {
        if ($this->has('latitude') && $this->has('longitude')) {
            $qb->addSelect(<<<SELECT
    (6371
    * (2 * ATAN(SQRT(SIN(((:latitude - s.latitude) * (PI()/180))/2) * SIN(((:latitude - s.latitude) * (PI()/180))/2)
    + COS(:latitude * (PI()/180)) * COS(s.latitude * (PI()/180))
    * SIN(((:longitude - s.longitude) * (PI()/180))/2) * SIN(((:longitude - s.longitude) * (PI()/180))/2)),
            SQRT(1-(sin(((:latitude - s.latitude) * (PI()/180))/2) * SIN(((:latitude - s.latitude) * (PI()/180))/2)
    + COS(:latitude * (PI()/180) * COS(s.latitude * (PI()/180)
    * SIN(((:longitude - s.longitude) * (PI()/180))/2) * SIN(((:longitude - s.longitude) * (PI()/180))/2))))))))
    AS distance
SELECT
);
            $qb->orderBy('distance');
            $qb->setParameter('latitude', $this->get('latitude'));
            $qb->setParameter('longitude', $this->get('longitude'));
        }
    }

 /**
  * @return QueryBuilder|null
  */
 public function getQueryBuilder()
 {
     if (is_null($this->queryBuilder)) {
         $this->queryBuilder = new QueryBuilder($this->connection);
         $this->adaptQueryBuilder($this->queryBuilder);
         // Apply filters
         $filters = $this->getFilterBuilder()->getCurrentFilters();
         foreach ($filters as $filter) {
             /* @var AbstractDBALFilterType $type */
             $type = $filter->getType();
             $type->setQueryBuilder($this->queryBuilder);
             $filter->apply();
         }
         // Apply sorting
         if (!empty($this->orderBy)) {
             $orderBy = $this->orderBy;
             $this->queryBuilder->orderBy($orderBy, $this->orderDirection == 'DESC' ? 'DESC' : 'ASC');
         }
     }
     return $this->queryBuilder;
 }

 /**
  * Specifies an ordering for the query results.
  * Replaces any previously specified orderings, if any.
  *
  * @param string $sort The ordering expression.
  * @param string $order The ordering direction.
  * @return QueryBuilder This QueryBuilder instance.
  */
 public function orderBy($sort, $order = null)
 {
     $connection = $this->getConnection();
     return parent::orderBy($connection->quoteIdentifier($sort), $order);
 }

 /**
  * Conditionally add LIMIT and ORDER BY to a QueryBuilder query.
  *
  * @param QueryBuilder $query
  * @param array        $options Additional options:
  *                              - 'limit' (integer):     Maximum number of results to return
  *                              - 'order' (string):      Field to order by
  *                              - 'direction' (string):  ASC or DESC
  *                              - 'contentid' (integer): Filter further by content ID
  *                              - 'id' (integer):        Filter by a specific change log entry ID
  */
 protected function setLimitOrder(QueryBuilder $query, array $options)
 {
     if (isset($options['order'])) {
         $query->orderBy($options['order'], $options['direction']);
     }
     if (isset($options['limit'])) {
         $query->setMaxResults(intval($options['limit']));
         if (isset($options['offset'])) {
             $query->setFirstResult(intval($options['offset']));
         }
     }
 }

 /**
  * 加载数据库结果集
  *
  * @param  bool $multiple 是否加载多行数据
  * @return $this|mixed
  */
 protected function _loadResult($multiple = false)
 {
     $this->_dbBuilder->from($this->db()->quoteIdentifier($this->_tableName), $this->db()->quoteIdentifier($this->_objectName));
     // 只获取单条记录
     if (false === $multiple) {
         $this->_dbBuilder->setMaxResults(1);
     }
     // 默认选择所有字段
     $this->_dbBuilder->addSelect($this->_buildSelect());
     // 处理排序问题
     if (!isset($this->_dbApplied['orderBy']) && !empty($this->_sorting)) {
         foreach ($this->_sorting as $column => $direction) {
             if (false === strpos($column, '.')) {
                 // Sorting column for use in JOINs
                 $column = $this->_objectName . '.' . $column;
             }
             $this->_dbBuilder->orderBy($column, $direction);
         }
     }
     if (true === $multiple) {
         $result = $this->_dbBuilder->execute();
         $result->setFetchMode(PDO::FETCH_CLASS, $this->_loadMultiResultFetcherClass(), $this->_loadMultiResultFetcherConstructor());
         $this->reset();
         return $result->fetchAll();
     } else {
         $result = $this->_dbBuilder->execute()->fetch();
         $this->reset();
         if ($result) {
             $this->_loadValues($result);
         } else {
             $this->clear();
         }
         return $this;
     }
 }

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

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

 /**
  * Modify database query for fetching the line time chart data
  *
  * @param  QueryBuilder $query
  * @param  string       $column name
  * @param  string       $tablePrefix
  */
 public function modifyTimeDataQuery(&$query, $column, $tablePrefix = 't')
 {
     // Convert time unitst to the right form for current database platform
     $dbUnit = $this->translateTimeUnit($this->unit);
     $limit = $this->countAmountFromDateRange($this->unit);
     $groupBy = '';
     if (isset($filters['groupBy'])) {
         $groupBy = ', ' . $tablePrefix . '.' . $filters['groupBy'];
         unset($filters['groupBy']);
     }
     $dateConstruct = 'DATE_FORMAT(' . $tablePrefix . '.' . $column . ', \'' . $dbUnit . '\')';
     $query->select($dateConstruct . ' AS date, COUNT(*) AS count')->groupBy($dateConstruct . $groupBy);
     $query->orderBy($dateConstruct, 'ASC')->setMaxResults($limit);
 }

 /**
  * Specifies an ordering for the query results.
  * Replaces any previously specified orderings, if any.
  *
  * @param string $sort The ordering expression.
  * @param string $order The ordering direction.
  *
  * @return \OCP\DB\QueryBuilder\IQueryBuilder This QueryBuilder instance.
  */
 public function orderBy($sort, $order = null)
 {
     $this->queryBuilder->orderBy($this->helper->quoteColumnName($sort), $order);
     return $this;
 }