Adds an ordering to the query results.
public addOrderBy ( string $sort, string $order = null ) | ||
$sort | string | The ordering expression. |
$order | string | The ordering direction. |
/** * @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; }
/** * @inheritdoc */ protected function setOrderBy(ColumnPriorityQueue $gridColumns, QueryBuilder $queryBuilder) { $queryBuilder->addOrderBy('module', 'ASC')->addOrderBy('main.title', 'DESC')->addOrderBy('main.id', 'DESC'); }
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'); } } }
/** * @param ListingRequest $context * @param QueryBuilder $builder */ private function addSortings(ListingRequest $context, QueryBuilder $builder) { foreach ($context->getSortings() as $sort) { if (!isset($sort['property'])) { continue; } $dir = 'ASC'; if (isset($sort['direction'])) { $dir = $sort['direction']; } $builder->addOrderBy($sort['property'], $dir); } }
/** * Adds an ordering to the query results. * * @param string $fieldName The fieldName to order by. Will be quoted according to database platform automatically. * @param string $order The ordering direction. * * @return QueryBuilder This QueryBuilder instance. */ public function addOrderBy(string $fieldName, string $order = null) : QueryBuilder { $this->concreteQueryBuilder->addOrderBy($this->connection->quoteIdentifier($fieldName), $order); return $this; }