groupBy example

$query = $this->connection->createQueryBuilder();
        $query->select([
            'streams.id',
            'streams.conditions',
            'GROUP_CONCAT(variant.ordernumber) as numbers',
        ]);
        $query->from('s_product_streams', 'streams');
        $query->leftJoin('streams', 's_product_streams_selection', 'articles', 'articles.stream_id = streams.id');
        $query->leftJoin('articles', 's_articles_details', 'variant', 'variant.articleID = articles.article_id AND variant.kind = 1');
        $query->where('streams.id IN (:ids)');
        $query->groupBy('streams.id');
        $query->setParameter(':ids', $streamIds, Connection::PARAM_INT_ARRAY);

        return $query->execute()->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_UNIQUE);
    }
}

$query = $this->database->select($this->getRevisionDataTable(), 'mlfr');
    $query->fields('mlfr', [$id_field]);
    $query->addExpression("MAX([mlfr].[$revision_field])", $revision_field);

    $query->join($this->getRevisionTable(), 'mlr', "[mlfr].[$revision_field] = [mlr].[$revision_field] AND [mlr].[$revision_default_field] = 0");

    $inner_select = $this->database->select($this->getRevisionDataTable(), 't');
    $inner_select->condition("t.$rta_field", '1');
    $inner_select->fields('t', [$id_field, $langcode_field]);
    $inner_select->addExpression("MAX([t].[$revision_field])", $revision_field);
    $inner_select
      ->groupBy("t.$id_field")
      ->groupBy("t.$langcode_field");

    $query->join($inner_select, 'mr', "[mlfr].[$revision_field] = [mr].[$revision_field] AND [mlfr].[$langcode_field] = [mr].[$langcode_field]");

    $query->groupBy("mlfr.$id_field");

    return $query->execute()->fetchAllKeyed(1, 0);
  }

}

            return;
        }

        /** @var \Doctrine\ORM\QueryBuilder $builder */
        $builder = $this->get('models')
                             ->getRepository('Shopware\Models\Snippet\Snippet')
                             ->createQueryBuilder('snippet');

        $builder->select('snippet.namespace, count(snippet.id) as snippetCount')
                ->groupBy('snippet.namespace')
                ->orderBy('snippet.namespace');

        $result = $builder->getQuery()->execute();

        $result = $this->prepareNamespaceTree($result);

        $this->View()->assign([
           'success' => true,
           'data' => $result,
           'total' => \count($result),
        ]);
    }

$context = $this->contextService->getShopContext();
        $categoryId = $context->getShop()->getCategory()->getId();

        $query = $this->connection->createQueryBuilder();
        $query->select(['manufacturer.id', 'manufacturer.name']);

        $query->from('s_articles_supplier', 'manufacturer');
        $query->innerJoin('manufacturer', 's_articles', 'product', 'product.supplierID = manufacturer.id')
            ->innerJoin('product', 's_articles_categories_ro', 'categories', 'categories.articleID = product.id AND categories.categoryID = :categoryId')
            ->setParameter(':categoryId', $categoryId);

        $query->groupBy('manufacturer.id');

        return $query->execute()->fetchAll(PDO::FETCH_ASSOC);
    }

    /** * Helper function to read the landing pages urls * * @return array */
    private function readLandingPageUrls()
    {
        

if ($denied_permissions) {
      $query->condition('hsi.permission', $denied_permissions, 'NOT IN');
    }
    $query->searchExpression($this->getKeywords(), $this->getType());

    $find = $query
      ->fields('i', ['langcode'])
      ->fields('hsi', ['section_plugin_id', 'topic_id'])
      // Since SearchQuery makes these into GROUP BY queries, if we add       // a field, for PostgreSQL we also need to make it an aggregate or a       // GROUP BY. In this case, we want GROUP BY.       ->groupBy('i.langcode')
      ->groupBy('hsi.section_plugin_id')
      ->groupBy('hsi.topic_id')
      ->limit(10)
      ->execute();

    // Check query status and set messages if needed.     $status = $query->getStatus();

    if ($status & SearchQuery::EXPRESSIONS_IGNORED) {
      $this->messenger->addWarning($this->t('Your search used too many AND/OR expressions. Only the first @count terms were included in this search.', ['@count' => $this->searchSettings->get('and_or_limit')]));
    }

    

private function getManufacturerIdsByCategoryId(int $categoryId, ShopContextInterface $context): array
    {
        $builder = $this->connection->createQueryBuilder();
        $builder->select('manufacturer.id')
                ->from('s_articles', 'article')
                ->innerJoin('article', 's_articles_categories_ro', 'article_cat_ro', 'article_cat_ro.articleID = article.id AND article_cat_ro.categoryID = :categoryId')
                ->innerJoin('article_cat_ro', 's_categories', 'category', 'category.id = article_cat_ro.categoryID AND category.active = 1')
                ->innerJoin('article', 's_articles_supplier', 'manufacturer', 'article.supplierID = manufacturer.id')
                ->leftJoin('article', 's_articles_avoid_customergroups', 'article_avoid_group', 'article.id = article_avoid_group.articleID AND article_avoid_group.customergroupID = :customerGroupId')
                ->where('article.active = 1')
                ->andWhere('article_avoid_group.articleID IS NULL')
                ->groupBy('manufacturer.id')
                ->orderBy('manufacturer.name')
                ->setMaxResults(12)
                ->setParameter('categoryId', $categoryId)
                ->setParameter('customerGroupId', $context->getCurrentCustomerGroup()->getId())
        ;

        return $builder->execute()->fetchAll(PDO::FETCH_COLUMN);
    }
}

    public function getDisplayDateFilterQueryBuilder($categoryIds, $filter, $shopId = null)
    {
        $builder = $this->getFilterQueryBuilder($categoryIds, $filter, $shopId);
        $builder->select([
            'DATE_FORMAT(blog.displayDate,\'%Y-%m\') as dateFormatDate',
            'COUNT(DISTINCT blog.id) as dateCount',
        ]);
        $builder->groupBy('dateFormatDate');

        return $builder;
    }

    /** * Returns an instance of the \Doctrine\ORM\Query object which select the blog author filter * * @param int[] $categoryIds * @param array $filter * @param int|null $shopId * * @return Query<Blog> */

  /** * Override the behavior of title(). Get the title of the revision. */
  public function titleQuery() {
    $titles = [];

    $results = $this->nodeStorage->getAggregateQuery()
      ->accessCheck(FALSE)
      ->allRevisions()
      ->groupBy('title')
      ->condition('vid', $this->value, 'IN')
      ->execute();

    foreach ($results as $result) {
      $titles[] = $result['title'];
    }

    return $titles;
  }

}

$query->leftJoin('vote.shop', 'shop');

        return $query;
    }

    protected function getSearchAssociationQuery($association, $model, $search)
    {
        $query = parent::getSearchAssociationQuery($association, $model, $search);

        if ($association == 'article') {
            $query->innerJoin('article.votes', 'votes');
            $query->groupBy('article.id');
        }

        return $query;
    }

    /** * @param array{success: true, data: array<array<string, mixed>>, total: int} $list * @param array<int, array<string, mixed>> $shops indexed by id * * @return array{success: true, data: array<array<string, mixed>>, total: int} */
    

  public function union(SelectInterface $query, $type = '') {
    $this->query->union($query, $type);
    return $this;
  }

  /** * {@inheritdoc} */
  public function groupBy($field) {
    $this->query->groupBy($field);
    return $this;
  }

  /** * {@inheritdoc} */
  public function forUpdate($set = TRUE) {
    $this->query->forUpdate($set);
    return $this;
  }

  

    private function getChildrenCountOfCategories($ids)
    {
        $query = $this->connection->createQueryBuilder();

        $query->select(['parent as id', 'COUNT(id) as childrenCount']);
        $query->from('s_categories', 'category')
            ->where('parent IN ( :ids )')
            ->andWhere('category.active = 1')
            ->groupBy('parent')
            ->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY);

        return $query->execute()->fetchAllKeyValue();
    }

    /** * Returns a associated array with the category id and the parent id * of the category. * The category id is used as array key and the parent id as array value. * * @param int[] $ids * * @return array */

    public function sGetAmountArticles()
    {
        $queryBuilder = $this->connection->createQueryBuilder();

        $queryBuilder->select('SUM(b.quantity*(floor(b.price * 100 + .55)/100)) AS totalAmount')
            ->from('s_order_basket', 'b')
            ->where('sessionID = :sessionId')
            ->andWhere('modus = 0')
            ->groupBy('sessionID')
            ->setParameter('sessionId', $this->session->get('sessionId'));

        $this->eventManager->notify(
            'Shopware_Modules_Basket_GetAmountArticles_QueryBuilder',
            [
                'queryBuilder' => $queryBuilder,
            ]
        );

        $result = $queryBuilder->execute()->fetch(\PDO::FETCH_ASSOC);

        

    public function getProductAmountPerCategory($categoryId, ?DateTimeInterface $from = null, ?DateTimeInterface $to = null)
    {
        $builder = $this->createProductAmountBuilder($from, $to)
            ->addSelect('categories.description as name')
            ->addSelect('( SELECT parent FROM s_categories WHERE categories.id = parent LIMIT 1 ) as node')
            ->innerJoin('articles', 's_articles_categories_ro', 'articleCategories', 'articles.id = articleCategories.articleID')
            ->innerJoin('articleCategories', 's_categories', 'categories', 'articleCategories.categoryID = categories.id')
            ->andWhere('categories.active = 1')
            ->groupBy('categories.id');

        if ($categoryId) {
            $builder->andWhere('categories.parent = :parent')
                ->setParameter('parent', $categoryId);
        }

        $builder = $this->eventManager->filter('Shopware_Analytics_ProductAmountPerCategory', $builder, [
            'subject' => $this,
        ]);

        return new Result($builder);
    }

$tablesSql = [];
        foreach ($tables as $table) {
            $query = $this->connection->createQueryBuilder();
            $alias = 'st' . $table['tableID'];

            $query->select(['MAX(sf.relevance * sm.relevance) as relevance', 'sm.keywordID', 'term']);
            $query->from('(' . $keywordSelection . ')', 'sm');
            $query->innerJoin('sm', 's_search_index', 'si', 'sm.keywordID = si.keywordID');
            $query->innerJoin('si', 's_search_fields', 'sf', 'si.fieldID = sf.id AND sf.relevance != 0 AND sf.tableID = ' . $table['tableID']);

            $query->groupBy('articleID')
                ->addGroupBy('sm.term')
                ->addGroupBy('sf.id');

            if (!empty($table['referenz_table'])) {
                $query->addSelect($alias . '.articleID as articleID');
                $query->innerJoin('si', $table['referenz_table'], $alias, 'si.elementID = ' . $alias . '.' . $table['foreign_key']);
            } elseif (!empty($table['foreign_key'])) {
                $query->addSelect($alias . '.id as articleID');
                $query->innerJoin('si', 's_articles', $alias, 'si.elementID = ' . $alias . '.' . $table['foreign_key']);
            } else {
                $query->addSelect('si.elementID as articleID');
            }

$query = $this->database->select($this->getRevisionDataTable(), 'tfr');
    $query->fields('tfr', [$id_field]);
    $query->addExpression("MAX([tfr].[$revision_field])", $revision_field);

    $query->join($this->getRevisionTable(), 'tr', "[tfr].[$revision_field] = [tr].[$revision_field] AND [tr].[$revision_default_field] = 0");

    $inner_select = $this->database->select($this->getRevisionDataTable(), 't');
    $inner_select->condition("t.$rta_field", '1');
    $inner_select->fields('t', [$id_field, $langcode_field]);
    $inner_select->addExpression("MAX([t].[$revision_field])", $revision_field);
    $inner_select
      ->groupBy("t.$id_field")
      ->groupBy("t.$langcode_field");

    $query->join($inner_select, 'mr', "[tfr].[$revision_field] = [mr].[$revision_field] AND [tfr].[$langcode_field] = [mr].[$langcode_field]");

    $query->groupBy("tfr.$id_field");

    return $query->execute()->fetchAllKeyed(1, 0);
  }

  /** * {@inheritdoc} */