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;
}
/**
* @test
*/
public function havingDelegatesToConcreteQueryBuilder()
{
$this->concreteQueryBuilder->having('uid=1', 'type=9')->shouldBeCalled()->willReturn($this->subject);
$this->subject->having('uid=1', 'type=9');
}
/**
* Modify the query to count occurences of a value in a column
*
* @param QueryBuilder $table without prefix
* @param string $uniqueColumn name
* @param array $options for special behavior
* @param string $tablePrefix
*/
public function modifyCountQuery(QueryBuilder &$query, $uniqueColumn, $options = [], $tablePrefix = 't')
{
$query->select('COUNT(' . $tablePrefix . '.' . $uniqueColumn . ') AS count');
// Count only unique values
if (!empty($options['getUnique'])) {
$selectAlso = '';
if (isset($options['selectAlso'])) {
$selectAlso = ', ' . implode(', ', $options['selectAlso']);
}
// Modify the previous query
$query->select($tablePrefix . '.' . $uniqueColumn . $selectAlso);
$query->having('COUNT(*) = 1')->groupBy($tablePrefix . '.' . $uniqueColumn . $selectAlso);
// Create a new query with subquery of the previous query
$uniqueQuery = $this->connection->createQueryBuilder();
$uniqueQuery->select('COUNT(' . $tablePrefix . '.' . $uniqueColumn . ') AS count')->from('(' . $query->getSql() . ')', $tablePrefix);
// Apply params from the previous query to the new query
$uniqueQuery->setParameters($query->getParameters());
// Replace the new query with previous query
$query = $uniqueQuery;
}
return $query;
}
/**
* Specifies a restriction over the groups of the query.
* Replaces any previous having restrictions, if any.
*
* @param mixed,... $having The restriction over the groups.
*
* @return QueryBuilder This QueryBuilder instance.
*/
public function having(...$having) : QueryBuilder
{
$this->concreteQueryBuilder->having(...$having);
return $this;
}
/**
* checks if the given result set matches all search terms
*
* @param QueryBuilder $query
* @param string $term
* @param Keyword[] $keywords
*/
private function addAndSearchLogic($query, $term, $keywords)
{
$searchTerms = $this->termHelper->splitTerm($term);
$searchTermMatchQueries = $this->createSearchTermMatchQueries($keywords, $searchTerms);
$totalSearchTermMatchesQuery = $this->connection->createQueryBuilder();
$totalSearchTermMatchesQuery->select('sum(matches)')->from('(' . $searchTermMatchQueries . ')', 'termMatches')->where('termMatches.elementID = product_id');
$query->addSelect('(' . $totalSearchTermMatchesQuery->getSQL() . ') AS searchTermMatches');
$query->having('searchTermMatches >= ' . count($searchTerms));
}
/**
* Specifies a restriction over the groups of the query.
* Replaces any previous having restrictions, if any.
*
* @param mixed $having The restriction over the groups.
*
* @return self
*/
public function having($having)
{
$this->qb->having($having);
return $this;
}
