getPriceTable example

 {
        $this->connection = $connection;
        $this->config = $config;
        $this->listingPriceHelper = $listingPriceHelper;
    }

    /** * @return \Doctrine\DBAL\Query\QueryBuilder */
    public function get(ShopContextInterface $context)
    {
        $priceTable = $this->listingPriceHelper->getPriceTable($context);

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

        $selection = 'MIN(' . $this->listingPriceHelper->getSelection($context) . ') as cheapest_price';

        $query->select(['prices.*', $selection]);
        $query->from('s_articles', 'product');
        $query->innerJoin('product', 's_core_tax', 'tax', 'tax.id = product.taxID');
        $query->innerJoin('product', '(' . $priceTable->getSQL() . ')', 'prices', 'product.id = prices.articleID');

        $this->joinAvailableVariant($query);
        


    public function setCriteria(Criteria $criteria)
    {
        $this->criteria = $criteria;
    }

    private function joinPrices(QueryBuilder $query, ShopContextInterface $context): void
    {
        $hasDifferentCustomerGroups = $this->hasDifferentCustomerGroups($context);

        $priceTable = $this->listingPriceHelper->getPriceTable($context);

        if ($hasDifferentCustomerGroups) {
            $priceTable->andWhere('IFNULL(customerPrice.`pseudoprice`, defaultPrice.`pseudoprice`) > 0');
        } else {
            $priceTable->andWhere('defaultPrice.pseudoprice > 0');
        }

        $query->innerJoin(
            'allVariants',
            '(' . $priceTable->getSQL() . ')',
            'variantPrices',
            

        $selection = $this->listingPriceHelper->getSelection($context);

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

        $query->select([
            'prices.*',
            'MIN(' . $selection . ') AS cheapest_price',
            'COUNT(DISTINCT price) as different_price_count',
        ]);

        $priceTable = $this->listingPriceHelper->getPriceTable($context);
        $priceTable->innerJoin('defaultPrice', 's_articles_details', 'details', 'details.id = defaultPrice.articledetailsID');
        $priceTable->andWhere('(details.laststock * details.instock) >= (details.laststock * details.minpurchase)');
        $priceTable->andWhere('details.active = 1');

        $query->from('s_articles', 'product');
        $query->innerJoin('product', '(' . $priceTable->getSQL() . ')', 'prices', 'product.id = prices.articleID');
        $query->innerJoin('prices', 's_articles_details', 'variant', 'variant.id = prices.articledetailsID AND variant.active = 1');
        $query->innerJoin('product', 's_core_tax', 'tax', 'tax.id = product.taxID');

        $this->joinAvailableVariant($query);
        $query->andWhere('prices.articledetailsID = availableVariant.id');

        
'prices.price as price',
            'relations.option_id',
            'options.group_id',
        ]);

        $query->from('s_articles_details', 'availableVariant');
        $query->innerJoin('availableVariant', 's_articles', 'product', 'availableVariant.articleId = product.id');
        $query->innerJoin('prices', 's_article_configurator_option_relations', 'relations', 'relations.article_id = prices.articledetailsID');
        $query->innerJoin('relations', 's_article_configurator_options', 'options', 'relations.option_id = options.id');

        $priceListingQuery = $this->connection->createQueryBuilder();
        $priceTable = $this->listingPriceHelper->getPriceTable($context);
        $priceTable->andWhere('defaultPrice.articledetailsID IN (:variants)');

        $priceListingQuery->select('DISTINCT prices.`articledetailsID` as articledetailsID');
        $priceListingQuery->addSelect('prices.`articleID` as articleID');
        $priceListingQuery->addSelect($this->listingPriceHelper->getSelection($context) . 'as price');
        $priceListingQuery->from('s_articles', 'product');
        $priceListingQuery->innerJoin('product', 's_articles_details', 'availableVariant', 'availableVariant.articleId = product.id');
        $priceListingQuery->innerJoin('prices', 's_article_configurator_option_relations', 'relations', 'relations.article_id = prices.articledetailsID');
        $priceListingQuery->innerJoin('relations', 's_article_configurator_options', 'options', 'relations.option_id = options.id');
        $priceListingQuery->innerJoin('product', 's_core_tax', 'tax', 'tax.id = product.taxID');
        $this->listingPriceHelper->joinPriceGroup($priceListingQuery);

        
Home | Imprint | This part of the site doesn't use cookies.