Magento 2 | Join table catalog_product_entity_tier_price to collection

Question

I'm trying to filter my product collection by tier price.

I currently have this code but it doesn't work and it returns error:

protected $_productCollectionFactory;
        
    public function __construct(
        \Magento\Backend\Block\Template\Context $context,        
        \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $productCollectionFactory,        
        array $data = []
    )
    {    
        $this->_productCollectionFactory = $productCollectionFactory;    
        parent::__construct($context, $data);
    }

public function getProductCollectionTot()
    {
        $collection = $this->_productCollectionFactory->create();
        $collection->addAttributeToSelect('*');
        
        
        $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;
    }

How can I filter my collection by tier price?

Answer 1

You have missed joining product collection with catalog_product_index_price table. Please make changes in your query as follows:

    public function getProductCollectionTot() {
        $collection = $this->_productCollectionFactory->create();
        $collection->addAttributeToSelect('*');

        $collection->getSelect()->join('catalog_product_index_price as price_index', 'e.entity_id = price_index.entity_id');    // Add this join statement
        $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;
    }

Hope this may help you. Please let me know if it worked for you or not. Happy to Help :)