CodeExplorer
You are a developer and looking for Shopware projects?
Apply Now!
addGroupBy example
/**
* Adds basic information to the summary query.
*
* Some basic summary behavior that doesn't need to be repeated as much as
* code that goes into summaryQuery()
*/
public function summaryBasics($count_field = TRUE) {
// Add the number of nodes counter $distinct = ($this->view->display_handler->getOption('distinct') && empty($this->query->no_distinct));
$count_alias = $this->query->addField($this->view->storage->get('base_table'), $this->view->storage->get('base_field'), 'num_records', ['count' => TRUE, 'distinct' => $distinct]);
$this->query->addGroupBy($this->name_alias);
if ($count_field) {
$this->query->setCountField($this->tableAlias, $this->realField);
}
$this->count_alias = $count_alias;
}
/** * Sorts the summary based upon the user's selection. * * The base variant of this is usually adequate. * * @param $order * The order selected in the UI. * @param string|null $by * (optional) This parameter sets the direction for which to order. * Defaults to NULL. */
public function summaryBasics($count_field = TRUE) {
// Add the number of nodes counter $distinct = ($this->view->display_handler->getOption('distinct') && empty($this->query->no_distinct));
$count_alias = $this->query->addField($this->view->storage->get('base_table'), $this->view->storage->get('base_field'), 'num_records', ['count' => TRUE, 'distinct' => $distinct]);
$this->query->addGroupBy($this->name_alias);
if ($count_field) {
$this->query->setCountField($this->tableAlias, $this->realField);
}
$this->count_alias = $count_alias;
}
/** * Sorts the summary based upon the user's selection. * * The base variant of this is usually adequate. * * @param $order * The order selected in the UI. * @param string|null $by * (optional) This parameter sets the direction for which to order. * Defaults to NULL. */
$query->from('s_categories', 'category')
->leftJoin('category', 's_categories_attributes', 'categoryAttribute', 'categoryAttribute.categoryID = category.id')
->leftJoin('category', 's_categories_avoid_customergroups', 'customerGroups', 'customerGroups.categoryID = category.id')
->leftJoin('category', 's_media', 'media', 'media.id = category.mediaID')
->leftJoin('media', 's_media_album_settings', 'mediaSettings', 'mediaSettings.albumID = media.albumID')
->leftJoin('media', 's_media_attributes', 'mediaAttribute', 'mediaAttribute.mediaID = media.id')
->leftJoin('category', 's_product_streams', 'stream', 'category.stream_id = stream.id')
->leftJoin('stream', 's_product_streams_attributes', 'productStreamAttribute', 'stream.id = productStreamAttribute.streamId')
->where('category.id IN (:categories)')
->andWhere('category.active = 1')
->andWhere('category.shops IS NULL OR category.shops LIKE :shopId')
->addGroupBy('category.id')
->setParameter(':categories', $ids, Connection::PARAM_INT_ARRAY)
->setParameter(':shopId', '%|' . $context->getShop()->getId() . '|%');
$this->fieldHelper->addCategoryTranslation($query, $context);
$this->fieldHelper->addMediaTranslation($query, $context);
$this->fieldHelper->addProductStreamTranslation($query, $context);
$this->fieldHelper->addCategoryMainDataTranslation($query, $context);
return $query;
}
->leftJoin('category', 's_categories_attributes', 'categoryAttribute', 'categoryAttribute.categoryID = category.id')
->leftJoin('category', 's_categories_avoid_customergroups', 'customerGroups', 'customerGroups.categoryID = category.id')
->leftJoin('category', 's_media', 'media', 'media.id = category.mediaID')
->leftJoin('media', 's_media_album_settings', 'mediaSettings', 'mediaSettings.albumID = media.albumID')
->leftJoin('media', 's_media_attributes', 'mediaAttribute', 'mediaAttribute.mediaID = media.id')
->leftJoin('category', 's_product_streams', 'stream', 'category.stream_id = stream.id')
->leftJoin('stream', 's_product_streams_attributes', 'productStreamAttribute', 'stream.id = productStreamAttribute.streamId')
->where('category.id IN (:categories)')
->andWhere('category.active = 1')
->andWhere('category.shops IS NULL OR category.shops LIKE :shopId')
->addGroupBy('category.id')
->setParameter(':categories', $ids, Connection::PARAM_INT_ARRAY)
->setParameter(':shopId', '%|' . $context->getShop()->getId() . '|%');
$this->fieldHelper->addCategoryTranslation($query, $context);
$this->fieldHelper->addMediaTranslation($query, $context);
$this->fieldHelper->addProductStreamTranslation($query, $context);
$this->fieldHelper->addCategoryMainDataTranslation($query, $context);
return $query;
}
$conditions = $criteria->getConditionsByClass(VariantCondition::class);
foreach ($conditions as $condition) {
$tmp->addBaseCondition($condition);
}
$query = $this->queryBuilderFactory->createQuery($tmp, $context);
$this->listingPriceSwitcher->joinPrice($query, $criteria, $context);
$query->select('listing_price.cheapest_price');
$query->setFirstResult(0);
$query->setMaxResults(1);
$query->addGroupBy('product.id');
return $query;
}
}
foreach ($conditions as $condition) {
$tmp->addBaseCondition($condition);
}
$query = $this->queryBuilderFactory->createQuery($tmp, $context);
$this->listingPriceSwitcher->joinPrice($query, $criteria, $context);
$query->select('listing_price.cheapest_price');
$query->setFirstResult(0);
$query->setMaxResults(1);
$query->addGroupBy('product.id');
return $query;
}
}
$builder = $this->addArticleCountSelect($builder, true);
$builder = $this->addChildrenCountSelect($builder);
if (isset($customerGroupId)) {
$builder->leftJoin('c.customerGroups', 'cg', Join::WITH, 'cg.id = :cgId')
->setParameter('cgId', $customerGroupId)
->andHaving('COUNT(cg.id) = 0');
}
// to prevent a temporary table and file sort we have to set the same sort and group by condition $builder->groupBy('c.parentId')
->addGroupBy('c.position')
->addGroupBy('c.id')
->orderBy('c.parentId', 'ASC')
->addOrderBy('c.position', 'ASC')
->addOrderBy('c.id', 'ASC');
return $builder;
}
private function addChildrenCountSelect(QueryBuilder $builder): QueryBuilder
{
$subQuery = $this->getEntityManager()->createQueryBuilder();
$builder = $this->addChildrenCountSelect($builder);
if (isset($customerGroupId)) {
$builder->leftJoin('c.customerGroups', 'cg', Join::WITH, 'cg.id = :cgId')
->setParameter('cgId', $customerGroupId)
->andHaving('COUNT(cg.id) = 0');
}
// to prevent a temporary table and file sort we have to set the same sort and group by condition $builder->groupBy('c.parentId')
->addGroupBy('c.position')
->addGroupBy('c.id')
->orderBy('c.parentId', 'ASC')
->addOrderBy('c.position', 'ASC')
->addOrderBy('c.id', 'ASC');
return $builder;
}
private function addChildrenCountSelect(QueryBuilder $builder): QueryBuilder
{
$subQuery = $this->getEntityManager()->createQueryBuilder();
private function getIdsQuery(Criteria $criteria, Context $context): QueryBuilder
{
$query = new QueryBuilder($this->connection);
$query = $this->criteriaQueryBuilder->build($query, $this->tagDefinition, $criteria, $context);
/** @var array<string> $select */
$select = array_merge(['LOWER(HEX(`tag`.`id`))'], $query->getQueryPart('select'));
$query->select($select);
$query->addGroupBy('`tag`.`id`');
$query->setMaxResults($criteria->getLimit());
$query->setFirstResult($criteria->getOffset() ?? 0);
return $query;
}
private function getTotal(QueryBuilder $query): int
{
$query->setMaxResults(null);
$query->setFirstResult(0);
$query->innerJoin(
'customer',
's_user_addresses',
'addresses',
'addresses.user_id = customer.id AND addresses.user_id NOT IN ( SELECT user_id FROM s_user_addresses WHERE (' . implode(' OR ', $ors) . ') )'
);
$query->addGroupBy('customer.id');
foreach ($condition->getCountryIds() as $i => $id) {
$query->setParameter(':HasNoAddressWithCountryCondition' . $i, $id);
}
}
}
'customer',
's_user_addresses',
'addresses',
'addresses.user_id = customer.id AND addresses.user_id NOT IN ( SELECT user_id FROM s_user_addresses WHERE (' . implode(' OR ', $ors) . ') )'
);
$query->addGroupBy('customer.id');
foreach ($condition->getCountryIds() as $i => $id) {
$query->setParameter(':HasNoAddressWithCountryCondition' . $i, $id);
}
}
}
// Add the keyword conditions, as is done in
// SearchQuery::prepareAndNormalize(), but simplified because we are
// only concerned with relevance ranking so we do not need to normalize.
$or = $this->view->query->getConnection()->condition('OR');
foreach ($words as $word) {
$or->condition("$search_index.word", $word);
}
$search_condition->condition($or);
// Add the GROUP BY and HAVING expressions to the query. $this->query->addWhere(0, $search_condition);
$this->query->addGroupBy("$search_index.sid");
$matches = $this->searchQuery->matches();
$placeholder = $this->placeholder();
$this->query->addHavingExpression(0, "COUNT(*) >= $placeholder", [$placeholder => $matches]);
}
// Set to NULL to prevent PDO exception when views object is cached // and to clear out memory. $this->searchQuery = NULL;
}
}
foreach ($words as $word) {
$or->condition("$search_index.word", $word);
}
$search_condition->condition($or);
// Add the GROUP BY and HAVING expressions to the query. $this->query->addWhere(0, $search_condition);
$this->query->addGroupBy("$search_index.sid");
$matches = $this->searchQuery->matches();
$placeholder = $this->placeholder();
$this->query->addHavingExpression(0, "COUNT(*) >= $placeholder", [$placeholder => $matches]);
}
// Set to NULL to prevent PDO exception when views object is cached // and to clear out memory. $this->searchQuery = NULL;
}
}
$query->select([
'articleID',
'COUNT(id) as total',
'points',
]);
$query->from('s_articles_vote', 'vote')
->where('vote.articleID IN (:products)')
->andWhere('vote.active = 1')
->groupBy('vote.articleID')
->addGroupBy('vote.points')
->orderBy('vote.articleID', 'ASC')
->setParameter(':products', $ids, Connection::PARAM_INT_ARRAY);
if ($this->config->get('displayOnlySubShopVotes')) {
$query->andWhere('(vote.shop_id = :shopId OR vote.shop_id IS NULL)');
$query->setParameter(':shopId', $context->getShop()->getId());
}
$data = $query->execute()->fetchAll(PDO::FETCH_GROUP);
$result = [];
'articleID',
'COUNT(id) as total',
'points',
]);
$query->from('s_articles_vote', 'vote')
->where('vote.articleID IN (:products)')
->andWhere('vote.active = 1')
->groupBy('vote.articleID')
->addGroupBy('vote.points')
->orderBy('vote.articleID', 'ASC')
->setParameter(':products', $ids, Connection::PARAM_INT_ARRAY);
if ($this->config->get('displayOnlySubShopVotes')) {
$query->andWhere('(vote.shop_id = :shopId OR vote.shop_id IS NULL)');
$query->setParameter(':shopId', $context->getShop()->getId());
}
$data = $query->execute()->fetchAll(PDO::FETCH_GROUP);
$result = [];
->leftJoin('product', 's_articles_img', 'img', 'img.articleID = product.id AND img.main = 1')
->leftJoin('img', 's_media', 'media', 'media.id = img.media_id')
->addSelect('SQL_CALC_FOUND_ROWS product.id as id')
->addSelect('product.name')
->addSelect('product.active')
->addSelect('ROUND(price.price*(100+tax.tax)/100,2) as price')
->addSelect('manual_sorting.position as position')
->addSelect('CONCAT("media/image/thumbnail/", img.img, "_140x140.", img.extension) as thumbnail')
->setParameter('categoryId', $categoryId)
->setMaxResults($limit)
->setFirstResult($start)
->addGroupBy('product.id');
$data['data'] = $query->execute()->fetchAll();
$data['data'] = array_map(function D$item) {
$item['thumbnail'] = $this->mediaService->getUrl($item['thumbnail']);
return $item;
}, $data['data']);
$data['total'] = $query->getConnection()->fetchColumn('SELECT FOUND_ROWS()');
->leftJoin('img', 's_media', 'media', 'media.id = img.media_id')
->addSelect('SQL_CALC_FOUND_ROWS product.id as id')
->addSelect('product.name')
->addSelect('product.active')
->addSelect('ROUND(price.price*(100+tax.tax)/100,2) as price')
->addSelect('manual_sorting.position as position')
->addSelect('CONCAT("media/image/thumbnail/", img.img, "_140x140.", img.extension) as thumbnail')
->setParameter('categoryId', $categoryId)
->setMaxResults($limit)
->setFirstResult($start)
->addGroupBy('product.id');
$data['data'] = $query->execute()->fetchAll();
$data['data'] = array_map(function D$item) {
$item['thumbnail'] = $this->mediaService->getUrl($item['thumbnail']);
return $item;
}, $data['data']);
$data['total'] = $query->getConnection()->fetchColumn('SELECT FOUND_ROWS()');
$query->innerJoin(
'variant',
's_articles',
'product',
'product.id = variant.articleID'
);
if ($this->config->get('hideNoInStock')) {
$query->andWhere('(variant.laststock * variant.instock) >= (variant.laststock * variant.minpurchase)');
}
$query->addGroupBy('variant.articleID');
$query->addGroupBy('variant.id');
$query->setParameter(':articleIds', $articleIds, Connection::PARAM_STR_ARRAY);
return $query->execute()->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_COLUMN);
}
/** * Fetches all groups with all options for provided products * * @return array<int, array<Group>> */
'variant',
's_articles',
'product',
'product.id = variant.articleID'
);
if ($this->config->get('hideNoInStock')) {
$query->andWhere('(variant.laststock * variant.instock) >= (variant.laststock * variant.minpurchase)');
}
$query->addGroupBy('variant.articleID');
$query->addGroupBy('variant.id');
$query->setParameter(':articleIds', $articleIds, Connection::PARAM_STR_ARRAY);
return $query->execute()->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_COLUMN);
}
/** * Fetches all groups with all options for provided products * * @return array<int, array<Group>> */
private function cleanupIndex(): void
{
$tables = $this->getSearchTables();
$qb = $this->connection->createQueryBuilder();
$qb
->select(['si.keywordID', 'si.fieldID', 'sk.keyword'])
->addSelect('(SELECT tableId FROM s_search_fields WHERE s_search_fields.id = si.fieldID) AS tableId')
->addSelect('COUNT(*) AS count_self')
->from('s_search_index', 'si')
->innerJoin('si', 's_search_keywords', 'sk', 'si.keywordID=sk.id')
->groupBy('si.keywordID')
->addGroupBy('si.fieldID')
->setParameter('threshold', static::INDEX_DELETE_THRESHOLD);
$selected = []; // To avoid identical selects foreach ($tables as $table) {
if (!empty($table['foreign_key'])) {
$qb->leftJoin(
'si',
$table['reference_table'] ?: 's_articles',
't' . $table['tableID'],
sprintf(
'si.elementID = t%d.%s AND si.fieldID IN (:fieldIDs%1$d)',
{
$tables = $this->getSearchTables();
$qb = $this->connection->createQueryBuilder();
$qb
->select(['si.keywordID', 'si.fieldID', 'sk.keyword'])
->addSelect('(SELECT tableId FROM s_search_fields WHERE s_search_fields.id = si.fieldID) AS tableId')
->addSelect('COUNT(*) AS count_self')
->from('s_search_index', 'si')
->innerJoin('si', 's_search_keywords', 'sk', 'si.keywordID=sk.id')
->groupBy('si.keywordID')
->addGroupBy('si.fieldID')
->setParameter('threshold', static::INDEX_DELETE_THRESHOLD);
$selected = []; // To avoid identical selects foreach ($tables as $table) {
if (!empty($table['foreign_key'])) {
$qb->leftJoin(
'si',
$table['reference_table'] ?: 's_articles',
't' . $table['tableID'],
sprintf(
'si.elementID = t%d.%s AND si.fieldID IN (:fieldIDs%1$d)',
private function addCondition(HasAddressWithCountryCondition $condition, QueryBuilder $query): void
{
$query->innerJoin(
'customer',
's_user_addresses',
'addresses',
'addresses.user_id = customer.id AND addresses.country_id IN (:HasAddressWithCountryCondition)'
);
$query->addGroupBy('customer.id');
$query->setParameter(':HasAddressWithCountryCondition', $condition->getCountryIds(), Connection::PARAM_INT_ARRAY);
}
}
protected $sqlExpressions = [];
/** * {@inheritdoc} */
public function execute() {
return $this
->prepare()
->addAggregate()
->compile()
->compileAggregate()
->addGroupBy()
->addSort()
->addSortAggregate()
->finish()
->result();
}
/** * {@inheritdoc} */
public function prepare() {
parent::prepare();
/** * {@inheritdoc} */
public function execute() {
return $this
->prepare()
->addAggregate()
->compile()
->compileAggregate()
->addGroupBy()
->addSort()
->addSortAggregate()
->finish()
->result();
}
/** * {@inheritdoc} */
public function prepare() {
parent::prepare();
$orderBy = ' ORDER BY ' . implode(', ', $parts);
$query->resetQueryPart('orderBy');
}
// order by is handled in group_concat $fieldCriteria->resetSorting();
$query->select([
'LOWER(HEX(' . $root . '.' . $localColumn . ')) as `key`',
'GROUP_CONCAT(LOWER(HEX(' . $root . '.' . $referenceColumn . ')) ' . $orderBy . ') as `value`',
]);
$query->addGroupBy($root . '.' . $localColumn);
if ($fieldCriteria->getLimit() !== null) {
$limitQuery = $this->buildManyToManyLimitQuery($association);
$params = [
'#source_column#' => EntityDefinitionQueryHelper::escape($association->getMappingLocalColumn()),
'#reference_column#' => EntityDefinitionQueryHelper::escape($association->getMappingReferenceColumn()),
'#table#' => $root,
];
$query->innerJoin(
$root,
$query->resetQueryPart('orderBy');
}
// order by is handled in group_concat $fieldCriteria->resetSorting();
$query->select([
'LOWER(HEX(' . $root . '.' . $localColumn . ')) as `key`',
'GROUP_CONCAT(LOWER(HEX(' . $root . '.' . $referenceColumn . ')) ' . $orderBy . ') as `value`',
]);
$query->addGroupBy($root . '.' . $localColumn);
if ($fieldCriteria->getLimit() !== null) {
$limitQuery = $this->buildManyToManyLimitQuery($association);
$params = [
'#source_column#' => EntityDefinitionQueryHelper::escape($association->getMappingLocalColumn()),
'#reference_column#' => EntityDefinitionQueryHelper::escape($association->getMappingReferenceColumn()),
'#table#' => $root,
];
$query->innerJoin(
$root,
$query->select([
'IFNULL(product.parent_id, product.id) as id',
'AVG(product_review.points) as average',
]);
$query->from('product_review');
$query->leftJoin('product_review', 'product', 'product', 'product.id = product_review.product_id OR product.parent_id = product_review.product_id');
$query->andWhere('product_review.status = 1');
$query->andWhere('product.id IN (:ids) OR product.parent_id IN (:ids)');
$query->andWhere('product.version_id = :version');
$query->setParameter('version', $versionId);
$query->setParameter('ids', Uuid::fromHexToBytesList($ids), ArrayParameterType::STRING);
$query->addGroupBy('IFNULL(product.parent_id, product.id)');
$averages = $query->executeQuery()->fetchAllAssociative();
$query = new RetryableQuery(
$this->connection,
$this->connection->prepare('UPDATE product SET rating_average = :average WHERE id = :id AND version_id = :version')
);
foreach ($averages as $average) {
$query->execute([
'average' => $average['average'],
'IFNULL(product.parent_id, product.id) as id',
'AVG(product_review.points) as average',
]);
$query->from('product_review');
$query->leftJoin('product_review', 'product', 'product', 'product.id = product_review.product_id OR product.parent_id = product_review.product_id');
$query->andWhere('product_review.status = 1');
$query->andWhere('product.id IN (:ids) OR product.parent_id IN (:ids)');
$query->andWhere('product.version_id = :version');
$query->setParameter('version', $versionId);
$query->setParameter('ids', Uuid::fromHexToBytesList($ids), ArrayParameterType::STRING);
$query->addGroupBy('IFNULL(product.parent_id, product.id)');
$averages = $query->executeQuery()->fetchAllAssociative();
$query = new RetryableQuery(
$this->connection,
$this->connection->prepare('UPDATE product SET rating_average = :average WHERE id = :id AND version_id = :version')
);
foreach ($averages as $average) {
$query->execute([
'average' => $average['average'],