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