getRelatedProductStreamFields example

 {
        $this->connection = $connection;
        $this->fieldHelper = $fieldHelper;
    }

    public function getQuery(array $ids, ShopContextInterface $context): QueryBuilder
    {
        $query = $this->connection->createQueryBuilder();

        $query->select($this->fieldHelper->getCategoryFields())
            ->addSelect($this->fieldHelper->getMediaFields())
            ->addSelect($this->fieldHelper->getRelatedProductStreamFields())
            ->addSelect('GROUP_CONCAT(customerGroups.customergroupID) as __category_customer_groups')
        ;

        $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')
            

public function getList($products, ShopContextInterface $context)
    {
        $ids = [];
        foreach ($products as $product) {
            $ids[] = $product->getId();
        }
        $ids = array_unique($ids);

        $query = $this->connection->createQueryBuilder();

        $query->select(['relation.article_id'])
            ->addSelect($this->fieldHelper->getRelatedProductStreamFields());

        $query->from('s_product_streams_articles', 'relation')
            ->innerJoin('relation', 's_product_streams', 'stream', 'stream.id = relation.stream_id')
            ->leftJoin('stream', 's_product_streams_attributes', 'productStreamAttribute', 'productStreamAttribute.streamID = stream.id')
            ->where('relation.article_id IN (:ids)')
            ->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY);

        $this->fieldHelper->addProductStreamTranslation($query, $context);

        $data = $query->execute()->fetchAll(PDO::FETCH_GROUP);