I'm using Magento v2.4, i want to retrieve a product collection that does not have special price (special price is not active or null) and tier price, currently i load the product collection like this:

public function __construct( ,        
    $this->_productCollectionFactory = $productCollectionFactory;     

public function getPurePriceProductCollection()
    $collection = $this->_productCollectionFactory->create();
    //need to filter out special price and tier price from product collection
    return $collection;
  • add below condtion. $collection->addAttributeToFilter('special_price',array('null' => true)); $collection->getSelect()->where("e.entity_id not in (select entity_id From catalog_product_entity_tier_price)");
    – S.P
    May 17, 2022 at 7:04

1 Answer 1


You can check for special price in addAttributeToFilter

  protected $productCollection;   
  public function __construct(
    \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $collectionFactory
    $this->productCollection = $collectionFactory;     
 public function execute()
 $now = date('Y-m-d');
    $specialpriceColl = $this->productCollection->create();        
                ->addAttributeToFilter('special_price', ['neq' => ''])
                ->addAttributeToFilter('special_from_date', [ 'lteq' => date('Y-m-d G:i:s', strtotime($now))])
                ->addAttributeToFilter('special_to_date', [ 'gteq' => date('Y-m-d G:i:s', strtotime($now))])                   
    $pcollection = $this->productCollection->create();
    $pcollection->addAttributeToFilter('special_price',array('null' => true));         
    $subquery = new \Zend_Db_Expr($specialpriceColl->getSelect()->reset(\Zend_Db_Select::COLUMNS)->columns('entity_id'));        

    $pcollection->getSelect()->where(" ((at_special_price.value IS NULL) OR  (e.entity_id not in ($subquery)) ) AND  (e.entity_id not in (select entity_id From catalog_product_entity_tier_price))");    
    foreach($pcollection as $coll)
        echo "<pre>";
  • Thanks, How about if there is a product that has inactive special price date ?
    – blue
    May 17, 2022 at 10:15
  • $collection->addAttributeToSelect('special_from_date',['lteq' => $now->format('Y-m-d H:i:s')]); and $collection->addAttributeToFilter('special_to_date',['gteq' => $now->format('Y-m-d H:i:s')]); use it.\
    – S.P
    May 17, 2022 at 10:48
  • not working i think need to put the special price in or condition with the date
    – blue
    May 18, 2022 at 1:14
  • check updated answer, You will get product collection item which has special price null or not valid for start and todate for current date and also not in tier price applied.
    – S.P
    May 28, 2022 at 20:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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