orderBy() public method

Replaces any previously specified orderings, if any.
public orderBy ( string $sort, string $order = null )
$sort string The ordering expression.
$order string The ordering direction.
Exemplo n.º 1
0
 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;
 }
Exemplo n.º 2
0
 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'));
     }
 }
Exemplo n.º 3
0
 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;
 }
Exemplo n.º 5
0
 /**
  * @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);
     }
 }
Exemplo n.º 6
0
 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;
 }
Exemplo n.º 9
0
 /**
  * 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);
 }
Exemplo n.º 10
0
 /**
  * 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']));
         }
     }
 }
Exemplo n.º 11
0
 /**
  * 加载数据库结果集
  *
  * @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;
     }
 }
Exemplo n.º 12
0
 /**
  * @test
  */
 public function orderByQuotesIdentifierAndDelegatesToConcreteQueryBuilder()
 {
     $this->connection->quoteIdentifier('aField')->shouldBeCalled()->willReturnArgument(0);
     $this->concreteQueryBuilder->orderBy('aField', null)->shouldBeCalled()->willReturn($this->subject);
     $this->subject->orderBy('aField');
 }
Exemplo n.º 13
0
 /**
  * {@inheritdoc}
  */
 public function orderBy($field, $direction)
 {
     return $this->queryBuilder->orderBy($field, $direction);
 }
Exemplo n.º 14
0
 /**
  * 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);
 }
Exemplo n.º 15
0
 /**
  * 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;
 }
Exemplo n.º 16
0
 /**
  * Specifies an ordering for the query results.
  * Replaces any previously specified orderings, if any.
  *
  * @param string $fieldName The fieldName to order by. Will be quoted according to database platform automatically.
  * @param string $order The ordering direction. No automatic quoting/escaping.
  *
  * @return QueryBuilder This QueryBuilder instance.
  */
 public function orderBy(string $fieldName, string $order = null) : QueryBuilder
 {
     $this->concreteQueryBuilder->orderBy($this->connection->quoteIdentifier($fieldName), $order);
     return $this;
 }
Exemplo n.º 17
0
 protected function executeSortBy($column, $direction = 'asc')
 {
     $this->query->orderBy($column, $direction);
 }
 /**
  * @param   QueryBuilder $builder The query builder
  *
  * @return  QueryBuilder
  */
 private function applyOrdering($builder)
 {
     foreach ($this->ordering as $column => $order) {
         $builder->orderBy($column, $order);
     }
     return $builder;
 }
Exemplo n.º 19
0
 /**
  * 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 self
  */
 public function orderBy($sort, $order = null)
 {
     $this->qb->orderBy($sort, $order);
     return $this;
 }
Exemplo n.º 20
0
 /**
  * Adds sorting parameters to the query.
  *
  * @param QueryBuilder $queryBuilder
  * the query
  * @param string|null $sortField
  * the sort field
  * @param boolean|null $sortAscending
  * true if sort ascending, false if descending
  */
 protected function addSort(QueryBuilder $queryBuilder, $sortField, $sortAscending)
 {
     if ($sortField !== null) {
         $type = $this->definition->getType($sortField);
         if ($type === 'many') {
             $sortField = $this->definition->getInitialSortField();
         }
         $order = $sortAscending === true ? 'ASC' : 'DESC';
         $queryBuilder->orderBy('`' . $sortField . '`', $order);
     }
 }
Exemplo n.º 21
0
 /**
  * Adds sorting parameters to the query.
  *
  * @param QueryBuilder $queryBuilder
  * the query
  * @param $sortField
  * the sort field
  * @param $sortAscending
  * true if sort ascending, false if descending
  */
 protected function addSort(QueryBuilder $queryBuilder, $sortField, $sortAscending)
 {
     if ($sortField !== null) {
         $order = $sortAscending === true ? 'ASC' : 'DESC';
         $queryBuilder->orderBy($sortField, $order);
     }
 }
Exemplo n.º 22
0
 function gridFiltersExt(\Doctrine\DBAL\Query\QueryBuilder &$dbalQuery, $params, $aColumnMapping)
 {
     if (is_array($aColumnMapping) && count($aColumnMapping) > 0) {
         foreach ($aColumnMapping as $value) {
             if ($value["ref"] == $params['sSortBy']) {
                 if (!$value["table"]) {
                     $params['sSortBy'] = $value["col"];
                 } else {
                     $params['sSortBy'] = $value["table"] . "." . $value["col"];
                 }
                 break;
             }
         }
     }
     if (is_array($params['aFilters'])) {
         foreach ($params['aFilters'] as $oFilter) {
             $sFieldname = $oFilter->field;
             if (is_array($aColumnMapping) && count($aColumnMapping) > 0) {
                 foreach ($aColumnMapping as $value) {
                     if ($value["ref"] == $sFieldname) {
                         /**
                          * Update Catalin:
                          * Exista situatii cand nu am nevoie de tabel.coloana ex cand folosesc functii gen GROUP_CONCAT
                          */
                         if (!$value["table"]) {
                             $sFieldname = $value["col"];
                         } else {
                             $sFieldname = $value["table"] . "." . $value["col"];
                         }
                         break;
                     }
                 }
             }
             $sValue = $oFilter->value;
             $sCompare = isset($oFilter->comparison) ? $oFilter->comparison : NULL;
             $sFilterType = $oFilter->type;
             switch ($sFilterType) {
                 case 'string':
                     $dbalQuery->andWhere($sFieldname . " like '%{$sValue}%'");
                     break;
                 case 'list':
                     if (strstr($sValue, ',')) {
                         $aValues = explode(',', $sValue);
                         $this->db->andWhere_in($sFieldname, $aValues);
                     } else {
                         $this->db->andWhere($sFieldname, $sValue);
                     }
                     break;
                 case 'boolean':
                     $this->db->andWhere($sFieldname, $sValue);
                     break;
                 case 'combo':
                     $this->db->andWhere($sFieldname, $sValue);
                     break;
                 case 'numeric':
                     switch ($sCompare) {
                         case 'eq':
                             $dbalQuery->andWhere($sFieldname . '=' . $sValue);
                             break;
                         case 'lt':
                             $dbalQuery->andWhere($sFieldname . '<' . $sValue);
                             break;
                         case 'gt':
                             $dbalQuery->andWhere($sFieldname . '>' . $sValue);
                             break;
                         case 'gte':
                             $dbalQuery->andWhere($sFieldname . '>=' . $sValue);
                             break;
                         case 'lte':
                             $dbalQuery->andWhere($sFieldname . '<=' . $sValue);
                             break;
                     }
                     break;
                 case 'date':
                     switch ($sCompare) {
                         case 'eq':
                             $dbalQuery->andWhere("DATE_FORMAT({$sFieldname}, '%Y-%m-%d')='" . date('Y-m-d', strtotime($sValue)) . "'");
                             break;
                         case 'lt':
                             $dbalQuery->andWhere("DATE_FORMAT({$sFieldname}, '%Y-%m-%d')<='" . date('Y-m-d', strtotime($sValue)) . "'");
                             break;
                         case 'gt':
                             $dbalQuery->andWhere("DATE_FORMAT({$sFieldname}, '%Y-%m-%d')>='" . date('Y-m-d', strtotime($sValue)) . "'");
                             break;
                     }
                     break;
             }
         }
     }
     if (!$params['bIsExport']) {
         $dbalQuery->orderBy($params['sSortBy'], $params['sDir']);
         $dbalQuery->setFirstResult($params['sStart']);
         $dbalQuery->setMaxResults($params['sLimit']);
     } else {
         //Limita maxima de inregistrari
         $dbalQuery->setMaxResults(50000);
     }
 }