addSelect example

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

            $tablesSql[] = $query->getSQL();
        }

        

/** * @return QueryBuilder */
    public function getQuery(array $numbers, ShopContextInterface $context)
    {
        $esdQuery = $this->getEsdQuery();
        $customerGroupQuery = $this->getCustomerGroupQuery();
        $availableVariantQuery = $this->getHasAvailableVariantQuery();
        $fallbackPriceQuery = $this->getPriceCountQuery(':fallback');
        $query = $this->connection->createQueryBuilder();
        $query->select($this->fieldHelper->getArticleFields())
            ->addSelect($this->fieldHelper->getTopSellerFields())
            ->addSelect($this->fieldHelper->getVariantFields())
            ->addSelect($this->fieldHelper->getUnitFields())
            ->addSelect($this->fieldHelper->getTaxFields())
            ->addSelect($this->fieldHelper->getPriceGroupFields())
            ->addSelect($this->fieldHelper->getManufacturerFields())
            ->addSelect($this->fieldHelper->getEsdFields())
            ->addSelect('(' . $esdQuery->getSQL() . ') as __product_has_esd')
            ->addSelect('(' . $customerGroupQuery->getSQL() . ') as __product_blocked_customer_groups')
            ->addSelect('(' . $availableVariantQuery->getSQL() . ') as __product_has_available_variants')
            ->addSelect('(' . $fallbackPriceQuery->getSQL() . ') as __product_fallback_price_count')
            ->addSelect('manufacturerMedia.id as __manufacturer_img_id')
        ;

    public function getVariantFacet()
    {
        if ($this->variantFacet !== false) {
            return $this->variantFacet;
        }

        $json = $this->connection->createQueryBuilder()
            ->addSelect('facet')
            ->from('s_search_custom_facet')
            ->where('unique_key = :key')
            ->andWhere('active = 1')
            ->setParameter('key', 'VariantFacet')
            ->execute()
            ->fetchColumn();

        if (empty($json)) {
            return $this->variantFacet = null;
        }

        

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

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

        $query->select($this->fieldHelper->getPriceFields());
        $query->addSelect('variants.ordernumber as number');
        $query->addSelect('GREATEST(price.from, variants.minpurchase) __price_from');

        $query->from('s_articles_prices', 'price')
            ->innerJoin('price', 's_articles_details', 'variants', 'variants.id = price.articledetailsID')
            ->leftJoin('price', 's_articles_prices_attributes', 'priceAttribute', 'priceAttribute.priceID = price.id')
            ->where('price.articledetailsID IN (:products)')
            ->andWhere('price.pricegroup = :customerGroup')
            ->andWhere($query->expr()->orX('price.to >= variants.minpurchase', 'price.to = "beliebig"'))
            ->orderBy('price.articledetailsID', 'ASC')
            ->addOrderBy('price.from', 'ASC')
            ->setParameter(':products', $ids, Connection::PARAM_INT_ARRAY)
            

$blogIds = [];

        foreach ($blogCategories as $blogCategory) {
            $blogIds[] = (int) $blogCategory['id'];
        }

        if (\count($blogIds) === 0) {
            return [];
        }

        $blogs = $this->modelManager->getConnection()->createQueryBuilder()
            ->addSelect('blog.id, blog.category_id, DATE(blog.display_date) as changed')
            ->from('s_blog', 'blog')
            ->where('blog.active = 1')
            ->innerJoin('blog', 's_categories', 'cat', 'cat.id = blog.category_id')
            ->andWhere('category_id IN (:ids)')
            ->andWhere('cat.shops IS NULL OR cat.shops LIKE :shopLike')
            ->andWhere('blog.shop_ids IS NULL OR blog.shop_ids LIKE :shopLike')
            ->setParameter(':shopLike', '%|' . $shopId . '|%')
            ->setParameter('ids', $blogIds, Connection::PARAM_INT_ARRAY)
            ->execute()
            ->fetchAllAssociative();

        

$result[$number] = $this->hydrator->hydrateProductImage($cover);
        }

        return $result;
    }

    private function getQuery(ShopContextInterface $context): QueryBuilder
    {
        $query = $this->connection->createQueryBuilder();

        $query->addSelect('variant.ordernumber as number')
            ->addSelect($this->fieldHelper->getMediaFields())
            ->addSelect($this->fieldHelper->getImageFields());

        $query->from('s_articles_img', 'image')
            ->innerJoin('image', 's_media', 'media', 'image.media_id = media.id')
            ->innerJoin('media', 's_media_album_settings', 'mediaSettings', 'mediaSettings.albumID = media.albumID')
            ->innerJoin('image', 's_articles_img', 'childImage', 'childImage.parent_id = image.id')
            ->innerJoin('image', 's_articles_details', 'variant', 'variant.id = childImage.article_detail_id')
            ->leftJoin('image', 's_media_attributes', 'mediaAttribute', 'mediaAttribute.mediaID = image.media_id')
            ->leftJoin('image', 's_articles_img_attributes', 'imageAttribute', 'imageAttribute.imageID = image.id');

        

|| (isset($parameters['sViewport']) && $parameters['sViewport'] === 'listing');
    }

    /** * @return array<array{id: int, locale: string}> */
    private function getLanguageShops(Shop $shop): array
    {
        $parentId = $shop->getParentId() ?: $shop->getId();

        return $this->connection->createQueryBuilder()
            ->addSelect('shop.id')
            ->addSelect('REPLACE(locale.locale, "_", "-") as locale')
            ->from('s_core_shops', 'shop')
            ->innerJoin('shop', 's_core_locales', 'locale', 'locale.id = shop.locale_id')
            ->where('shop.id = :shopId')
            ->orWhere('shop.main_id = :shopId')
            ->andWhere('active=1')
            ->setParameter('shopId', $parentId)
            ->execute()
            ->fetchAll();
    }

    

// no matching products found by the search query builder.         // add condition that the result contains no product.         if ($searchQuery === null) {
            $query->andWhere('0 = 1');

            return;
        }

        $queryString = $searchQuery->getSQL();

        $query->addSelect('searchTable.*');
        $query->addState(self::STATE_INCLUDES_RANKING);

        $query->innerJoin(
            'product',
            '(' . $queryString . ')',
            'searchTable',
            'searchTable.product_id = product.id'
        );
    }
}

public function getName()
    {
        return self::NAME;
    }

    /** * {@inheritdoc} */
    public function getUrls(Context $context, $limit = null, $offset = null)
    {
        $qb = $this->getBaseQuery()
            ->addSelect(['DISTINCT details.articleID', 'details.ordernumber'])
            ->orderBy('details.articleID', 'ASC')
            ->setParameter(':shop', $context->getShopId());

        if ($limit !== null && $offset !== null) {
            $qb->setFirstResult($offset)
                ->setMaxResults($limit);
        }

        $result = $qb->execute()->fetchAll();

        if (!\count($result)) {
            

public function getName(): string
    {
        return self::NAME;
    }

    /** * {@inheritdoc} */
    public function getUrls(Context $context, $limit = null, $offset = null): array
    {
        $qb = $this->getBaseQuery()
            ->addSelect(['id'])
            ->setParameter(':shop', $context->getShopId())
            ->orderBy('id');

        if ($limit !== null && $offset !== null) {
            $qb->setFirstResult($offset)
                ->setMaxResults($limit);
        }

        $result = $qb->execute()->fetchAll();

        if (!\count($result)) {
            

        return $this->getShopArrayByHostAlias($host);
    }

    /** * @return QueryBuilder */
    public function getQueryBuilder()
    {
        return $this->createQueryBuilder('shop')
            ->addSelect('shop')

            ->addSelect('main')
            ->leftJoin('shop.main', 'main')

            ->addSelect('locale')
            ->leftJoin('shop.locale', 'locale')

            ->addSelect('currency')
            ->leftJoin('shop.currency', 'currency')

            ->addSelect('template')
            

->execute()
            ->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_UNIQUE | PDO::FETCH_ASSOC);

        if (empty($this->cart)) {
            return;
        }

        $ids = array_keys($this->cart);

        $qb = $this->connection->createQueryBuilder();
        $this->cartAttributes = $qb->from('s_order_basket_attributes', 'cartAttributes')
            ->addSelect('cartAttributes.basketID')
            ->addSelect('cartAttributes.*')
            ->andWhere(
                $qb->expr()->in('cartAttributes.basketID', $ids)
            )
            ->execute()
            ->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_UNIQUE | PDO::FETCH_ASSOC);
    }

    public function persist(): void
    {
        foreach ($this->cart as $id => $item) {
            

$fields[$field->getStorageName()] = $field;

                continue;
            }
            if ($field->is(PrimaryKey::class)) {
                $fields[$field->getStorageName()] = $field;
            }
        }

        /** @var StorageAware $field */
        foreach ($fields as $field) {
            $query->addSelect(
                EntityDefinitionQueryHelper::escape($table) . '.' . EntityDefinitionQueryHelper::escape($field->getStorageName())
            );
        }

        $query = $this->criteriaQueryBuilder->build($query, $definition, $criteria, $context);

        if (!empty($criteria->getIds())) {
            $this->queryHelper->addIdCondition($criteria, $definition, $query);
        }

        $this->addGroupBy($definition, $criteria, $context, $query, $table);

        

return array_shift($manufacturers);
    }

    /** * {@inheritdoc} */
    public function getList(array $ids, ShopContextInterface $context)
    {
        $query = $this->connection->createQueryBuilder();

        $query->addSelect($this->fieldHelper->getManufacturerFields());
        $query->addSelect('media.id as __manufacturer_img_id');

        $query->from('s_articles_supplier', 'manufacturer')
            ->leftJoin('manufacturer', 's_articles_supplier_attributes', 'manufacturerAttribute', 'manufacturerAttribute.supplierID = manufacturer.id')
            ->leftJoin('manufacturer', 's_media', 'media', 'media.path = manufacturer.img')
            ->where('manufacturer.id IN (:ids)')
            ->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY);

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

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

return $mapping;
    }

    private function createSeoMapping(SalesChannelContext $context, array $mapping): array
    {
        if (empty($mapping)) {
            return [];
        }

        $query = $this->connection->createQueryBuilder();
        $query->addSelect(['seo_path_info', 'path_info']);

        $query->from('seo_url');
        $query->andWhere('seo_url.is_canonical = 1');
        $query->andWhere('seo_url.path_info IN (:pathInfo)');
        $query->andWhere('seo_url.language_id = :languageId');
        $query->andWhere('seo_url.sales_channel_id = :salesChannelId OR seo_url.sales_channel_id IS NULL');
        $query->andWhere('is_deleted = 0');
        $query->setParameter('pathInfo', $mapping, ArrayParameterType::STRING);
        $query->setParameter('languageId', Uuid::fromHexToBytes($context->getContext()->getLanguageId()));
        $query->setParameter('salesChannelId', Uuid::fromHexToBytes($context->getSalesChannelId()));
        $query->addOrderBy('seo_url.sales_channel_id');