$qb = $conn->createQueryBuilder() ->select('u.name') ->from('users', 'u') ->groupBy('u.id');
/** * 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; }
/** * @test */ public function groupByQuotesIdentifierAndDelegatesToConcreteQueryBuilder() { $this->connection->quoteIdentifiers(['aField', 'anotherField'])->shouldBeCalled()->willReturnArgument(0); $this->concreteQueryBuilder->groupBy('aField', 'anotherField')->shouldBeCalled()->willReturn($this->subject); $this->subject->groupBy('aField', 'anotherField'); }
/** * Specifies a grouping over the results of the query. * Replaces any previously specified groupings, if any. * * @param mixed,... $groupBy The grouping expression. * * @return QueryBuilder This QueryBuilder instance. */ public function groupBy(...$groupBy) : QueryBuilder { $this->concreteQueryBuilder->groupBy(...$this->quoteIdentifiers($groupBy)); return $this; }
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; } 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 (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; }
public function groupBy($groupBy) { $connection = $this->getConnection(); return parent::groupBy($connection->quoteIdentifier($groupBy)); }
/** * Specifies a grouping over the results of the query. * Replaces any previously specified groupings, if any. * * @param mixed $groupBy The grouping expression. * * @return self */ public function groupBy($groupBy) { $this->qb->groupBy($groupBy); return $this; }
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; }
/** * @param QueryBuilder $q * @param array $tables $tables[0] should be primary table * @param bool $innerJoinTables * @param null $whereExpression * @param null $having */ protected function applySearchQueryRelationship(QueryBuilder $q, array $tables, $innerJoinTables, $whereExpression = null, $having = null) { $primaryTable = $tables[0]; unset($tables[0]); $joinType = $innerJoinTables ? 'join' : 'leftJoin'; $this->useDistinctCount = true; $joins = $q->getQueryPart('join'); if (!array_key_exists($primaryTable['alias'], $joins)) { $q->{$joinType}($primaryTable['from_alias'], MAUTIC_TABLE_PREFIX . $primaryTable['table'], $primaryTable['alias'], $primaryTable['condition']); foreach ($tables as $table) { $q->{$joinType}($table['from_alias'], MAUTIC_TABLE_PREFIX . $table['table'], $table['alias'], $table['condition']); } if ($whereExpression) { $q->andWhere($whereExpression); } if ($having) { $q->andHaving($having); } $q->groupBy('l.id'); } }