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