PHP QueryBuilder::orderBy方法代碼示例

 /**
  *
  * @return boolean
  */
 public function process()
 {
     $this->form->submit($this->request);
     if ($this->form->isValid()) {
         $formData = $this->form->getData();
         $name = (string) $formData->getName();
         if ($name) {
             $this->qb->andWhere($this->qb->expr()->like('p.name', ':name'))->setParameter('name', '%' . $name . '%');
         }
         $text = (string) $formData->getText();
         if ($text) {
             $this->qb->andWhere($this->qb->expr()->orx($this->qb->expr()->like('p.intro', ':intro'), $this->qb->expr()->like('p.content', ':content')))->setParameter('intro', '%' . $text . '%')->setParameter('content', '%' . $text . '%');
         }
         $isPublished = (string) $formData->getIsPublished();
         if ($isPublished) {
             if ($isPublished == 'yes') {
                 $this->qb->andWhere($this->qb->expr()->eq('p.status', ':status'))->setParameter('status', PostStatus::PUBLISHED);
             }
             if ($isPublished == 'no') {
                 $this->qb->andWhere($this->qb->expr()->neq('p.status', ':status'))->setParameter('status', PostStatus::PUBLISHED);
             }
         }
         $order = (string) $formData->getOrder();
         if ($order) {
             $this->qb->orderBy('p.' . $order, 'DESC');
         }
         return true;
     }
     return false;
 }

 /**
  * Build and returns Query for select rows
  * @param \Doctrine\ORM\QueryBuilder $queryBuilder
  * @param \EMC\TableBundle\Provider\QueryConfigInterface $queryConfig
  * @param array $mapping
  * @return \Doctrine\ORM\Query
  */
 private function getQueryRows(QueryBuilder $queryBuilder, QueryConfigInterface $queryConfig, array &$mapping)
 {
     $queryBuilder->resetDQLPart('select');
     $limit = $queryConfig->getLimit();
     $page = $queryConfig->getPage();
     $select = $queryConfig->getSelect();
     $orderBy = $queryConfig->getOrderBy();
     if ($limit > 0) {
         $queryBuilder->setMaxResults($limit)->setFirstResult(($page - 1) * $limit);
     }
     $mapping = array_map(function ($i) {
         return 'col' . $i;
     }, array_flip($select));
     foreach ($mapping as $column => $name) {
         $queryBuilder->addSelect($column . ' AS ' . $name);
     }
     if (count($orderBy) === 0) {
         $queryBuilder->orderBy($queryBuilder->getRootAlias() . '.id', 'ASC');
     } else {
         foreach ($orderBy as $column => $isAsc) {
             $queryBuilder->orderBy($column, $isAsc ? 'ASC' : 'DESC');
         }
     }
     return $queryBuilder->getQuery();
 }

 /**
  * @param ClientConditionInterface $condition
  * @return Client[]
  */
 public function search(ClientConditionInterface $condition)
 {
     $this->qb = $this->em->createQueryBuilder();
     $this->qb->select('c')->from('CoreBundle:Client', 'c');
     $this->initQueryBuilder($condition);
     $this->preparePagination($this->qb, $condition);
     $this->qb->orderBy('c.createdAt', 'DESC');
     return $this->qb->getQuery()->getResult();
 }

 /**
  * Get the query result
  *
  * @param integer $limit
  *
  * @return ArrayCollection
  */
 public function getQueryResults($limit = null)
 {
     if (isset($limit)) {
         $this->queryBuilder->setMaxResults($limit);
     }
     if (!$this->queryBuilder->getDQLPart('orderBy')) {
         $this->queryBuilder->orderBy('a.createdAt', 'ASC');
     }
     return $this->queryBuilder->getQuery()->getResult();
 }

 public function getItems()
 {
     $this->qb->setMaxResults($this->getLimit());
     $this->qb->setFirstResult($this->getOffset());
     list($sortColumn, $sortType) = $this->getSorting();
     if ($sortColumn) {
         $this->qb->orderBy($this->qb->getRootAlias() . "." . $sortColumn, $sortType);
     }
     return $this->qb->getQuery()->getResult();
 }

 protected function order()
 {
     $column = $this->getParamAdapter()->getColumn();
     $order = $this->getParamAdapter()->getOrder();
     if (!$column) {
         return;
     }
     $header = $this->getTable()->getHeader($column);
     $tableAlias = $header ? $header->getTableAlias() : 'q';
     if ($column) {
         $this->query->orderBy($tableAlias . '.' . $column, $order);
     }
 }

 public function sort(array $p_cols)
 {
     foreach (array_keys($p_cols) as $id => $property) {
         if (!is_string($property)) {
             // not sortable
             continue;
         }
         if (@in_array($id, $p_params['sortCol'])) {
             $dir = @in_array($id, $p_params['sortDir']) ?: 'asc';
             $this->_queryObject->orderBy("e.{$property}", $dir);
         }
     }
 }

 /**
  * Parse order conditions and add it to query
  *
  * @param string|null $sort
  * @param string|null $order
  */
 public function orderBy($sort = null, $order = null)
 {
     if (is_string($sort)) {
         if (strpos($sort, '.') > 0) {
             $parts = explode('.', $sort);
             if (count($parts) >= 2 && array_key_exists($parts[0], $this->metadata->associationMappings)) {
                 $this->qBuilder->innerJoin('e.' . $parts[0], $parts[0], Join::WITH);
                 $this->qBuilder->orderBy($parts[0] . '.' . $parts[1], $order);
             }
         } else {
             if (array_key_exists($sort, $this->metadata->fieldMappings)) {
                 $this->qBuilder->orderBy('e.' . $sort, $order);
             }
         }
     }
 }

 /**
  * @param QueryBuilder $qb
  * @param string $alias
  * @param string|null $sortField
  * @param string|null $sortOrder
  * @return QueryBuilder
  */
 protected function sorting(QueryBuilder $qb, string $alias, string $sortField = null, string $sortOrder = null)
 {
     if (!empty($sortField) && (strtolower($sortOrder) == 'asc' || strtolower($sortOrder) == 'desc')) {
         $qb->orderBy($alias . '.' . $sortField, $sortOrder);
     }
     return $qb;
 }

 public static function apply(QueryBuilder $qb, array $orderBy)
 {
     foreach ($orderBy as $field => $sortOrder) {
         $qb->orderBy('a.' . Camelizer::camelize($field), $sortOrder);
     }
     return $qb;
 }

 /**
  * @param QueryBuilder $qb
  * @param string       $dqlAlias
  */
 public function modify(QueryBuilder $qb, $dqlAlias)
 {
     if ($this->dqlAlias !== null) {
         $dqlAlias = $this->dqlAlias;
     }
     $qb->orderBy(sprintf('%s.%s', $dqlAlias, $this->field), $this->order);
 }

 /**
  * set order
  * 
  * @param string $order_field
  * @param string $order_type
  * 
  * @return Datatable 
  */
 public function setOrder($order_field, $order_type)
 {
     $this->order_field = $order_field;
     $this->order_type = $order_type;
     $this->queryBuilder->orderBy($order_field, $order_type);
     return $this;
 }

 /**
  * {@inheritdoc}
  */
 public function filter(Request $request, QueryBuilder $queryBuilder)
 {
     $order = $request->query->get('order') ?: self::DEFAULT_ORDER;
     if ($request->query->has('sort')) {
         $alias = $queryBuilder->getRootAliases()[0];
         $queryBuilder->orderBy($alias . '.' . $request->query->get('sort'), $order);
     }
 }

 /**
  * Apply the sortings to the query builder
  *
  * @param QueryBuilder $queryBuilder
  * @param Sort         $sortCollectionDefinition
  *
  * @return \Doctrine\ORM\QueryBuilder
  */
 public function sort(QueryBuilder $queryBuilder, Sort $sortCollectionDefinition, $uniqueNameToQueryFieldMap)
 {
     /** @var $sortDefinition SortCondition */
     foreach ($sortCollectionDefinition as $sortDefinition) {
         $identifier = $uniqueNameToQueryFieldMap[$sortDefinition->getFieldName()];
         $queryBuilder->orderBy($identifier, $sortDefinition->getDirection());
     }
     return $queryBuilder;
 }

 /**
  * @param $columns \Sorien\DataGridBundle\Grid\Column\Column[]
  * @param $page int Page Number
  * @param $limit int Rows Per Page
  * @return \Sorien\DataGridBundle\Grid\Rows
  */
 public function execute($columns, $page = 0, $limit = 0)
 {
     $this->query = $this->manager->createQueryBuilder($this->class);
     $this->query->from($this->class, self::TABLE_ALIAS);
     $where = $this->query->expr()->andx();
     $sorted = false;
     foreach ($columns as $column) {
         $this->query->addSelect($this->getFieldName($column));
         if ($column->isSorted() && !$column->isDefaultSort()) {
             $this->query->orderBy($this->getFieldName($column, false), $column->getOrder());
             $sorted = true;
         } elseif (!$sorted && $column->isSorted() && $column->isDefaultSort()) {
             $this->query->orderBy($this->getFieldName($column, false), $column->getOrder());
         }
         if ($column->isFiltered()) {
             if ($column->getFiltersConnection() == column::DATA_CONJUNCTION) {
                 foreach ($column->getFilters() as $filter) {
                     $operator = $this->normalizeOperator($filter->getOperator());
                     $where->add($this->query->expr()->{$operator}($this->getFieldName($column, false), $this->normalizeValue($filter->getOperator(), $filter->getValue())));
                 }
             } elseif ($column->getFiltersConnection() == column::DATA_DISJUNCTION) {
                 $sub = $this->query->expr()->orx();
                 foreach ($column->getFilters() as $filter) {
                     $operator = $this->normalizeOperator($filter->getOperator());
                     $sub->add($this->query->expr()->{$operator}($this->getFieldName($column, false), $this->normalizeValue($filter->getOperator(), $filter->getValue())));
                 }
                 $where->add($sub);
             }
             $this->query->where($where);
         }
     }
     foreach ($this->joins as $alias => $field) {
         $this->query->leftJoin($field, $alias);
     }
     if ($page > 0) {
         $this->query->setFirstResult($page * $limit);
     }
     if ($limit > 0) {
         $this->query->setMaxResults($limit);
     }
     //call overridden prepareQuery or associated closure
     $query = $this->prepareQuery(clone $this->query);
     $items = $query->getQuery()->getResult();
     // hydrate result
     $result = new Rows();
     foreach ($items as $item) {
         $row = new Row();
         foreach ($item as $key => $value) {
             $row->setField($key, $value);
         }
         //call overridden prepareRow or associated closure
         if (($modifiedRow = $this->prepareRow($row)) != null) {
             $result->addRow($modifiedRow);
         }
     }
     return $result;
 }