3

I want to create a page or tab within customer dashboards that shows all products that have a tier price for a customer group they are a member of. This will help customers who have contract prices for certain products to quickly browse and add these products to cart.

protected function _getProductCollection()
{
    /** @var $collection \Magento\Catalog\Model\ResourceModel\Product\Collection */
    $collection = $this->_productCollectionFactory->create();
    $collection->setVisibility($this->_catalogProductVisibility->getVisibleInCatalogIds());

    $collection = $this->_addProductAttributesAndPrices(
        $collection
    )->setPageSize(
        $this->getProductsCount()
    )->setCurPage(
        1
    );

    $collection->addTierPrice()
          ->getSelect()
          ->where('price_index.tier_price < price_index.price');

    $collection->where(cust_group = $customerGroup);
    return $collection;
}

How is a collection like above filtered to achieve this?

UPDATE

Unsure if filtering with customer group is really necessary magento collections seem to just do this anyway as error shows when playing with these collections:

2 exception(s):
Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 3, query was: SELECT COUNT(DISTINCT e.entity_id) FROM `catalog_product_flat_2` AS `e`
 INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=2 AND cat_index.visibility IN(2, 4) AND cat_index.category_id='2'
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '2' AND price_index.customer_group_id = '4' WHERE (price_index.Final_price < price_index.price) AND (cust_group =)
Exception #1 (PDOException): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 3

So I feel like i just need it to pull products that have a tier price and then it will show the products i need.

->where('price_index.tier_price < price_index.price');

The above filter works giving me the products with tier prices however this also shows the products for all groups also which i need to filter out. Something like below:

->where('price_index.Tier_price < price_index.price')
->where('price_index.customer_group_id != "ALL GROUPS"');

Upon looking at the database in the "catalog_product_index_price" price table there just seems to be a record set for each group id when all group is selected, so to solve this i think I can join this table to the 'catalog_product_entity_tier_price' table so that i can then filter out products that have the all_groups row set to 1 as this seems to have the data i need.

So i have added a join to the collection and filtered by all tier prices set to 0 to remove the tier prices for all groups:

 $collection->getSelect()->join('catalog_product_entity_tier_price as tier', 'price_index.entity_id = tier.entity_id');

 $collection->addFinalPrice()
          ->addMinimalPrice()
          ->getSelect()
          ->where('tier.all_groups = 0')
          ->where('price_index.Tier_price < price_index.price');

This seems to work perfectly allowing me to remove all products with tier prices set to "all_group" leaving just my collection of tier priced products within signed in users customer group. Feel a bit gutted i wasted the bounty now :P

SOLVED

4

It's possible to do this with the following collection:

protected function _getProductCollection()
{
    /** @var $collection \Magento\Catalog\Model\ResourceModel\Product\Collection */
    $collection = $this->_productCollectionFactory->create();
    $collection->setVisibility($this->_catalogProductVisibility->getVisibleInCatalogIds());

    $collection = $this->_addProductAttributesAndPrices(
        $collection
    )->setPageSize(
        $this->getProductsCount()
    )->setCurPage(
        1
    );

    $collection->getSelect()->join('catalog_product_entity_tier_price as tier', 'price_index.entity_id = tier.entity_id');

    $collection->addFinalPrice()
          ->addMinimalPrice()
          ->getSelect()
          ->where('tier.all_groups = 0')
          ->where('price_index.Tier_price < price_index.price');

    return $collection;
}

This gets all products with tier prices except for ones set in the all group so just the tier prices for that particular group are shown.

Since updating to 2.2.1 I'm not sure why but my Tier_Price column within price_index seems null for everything so returns an empty collection. For now i have overcome this by taking the tier price directly from the catalog_product_entity_tier_price. The where method now looks like below:

->where('tier.value < price_index.price');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.