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; }
/** * 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; }
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; }
/** * 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; }
/** * 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); } }
/** * 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); } }
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); } }