0

I need to create a page which lists all special products (with active special price). How can I do this?

1

2 Answers 2

4

This is how I did it

    $objectManager   = \Magento\Framework\App\ObjectManager::getInstance();

    $visibleProducts = $objectManager->create('\Magento\Catalog\Model\Product\Visibility')->getVisibleInCatalogIds();
    $collection = $objectManager->create('\Magento\Catalog\Model\ResourceModel\Product\Collection')->setVisibility($visibleProducts);

    $collection = $this->_addProductAttributesAndPrices($collection);

    $stockFilter = $objectManager->create('\Magento\CatalogInventory\Helper\Stock');
    $stockFilter->addInStockFilterToCollection($collection);

    $collection    
        ->addAttributeToFilter(
            'special_price',
            ['gt'=>0], 'left'
        )->addAttributeToFilter(
            'special_from_date',['or' => [ 0 => ['date' => true, 
                                                'to' => date('Y-m-d',time()).' 23:59:59'],
                                          1 => ['is' => new \Zend_Db_Expr(
                                             'null'
                                         )],]], 'left'
        )->addAttributeToFilter(
            'special_to_date',  ['or' => [ 0 => ['date' => true,
                                               'from' => date('Y-m-d',time()).' 00:00:00'],
                                         1 => ['is' => new \Zend_Db_Expr(
                                             'null'
                                         )],]], 'left'
        );
2
  • 1
    How can we display this products in cms page we have created?
    – srg
    Sep 14, 2017 at 7:10
  • Hello @Anna, this working for me, but when I enabled the flat catalog this is showing the following MySQL error: Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.special_from_date' in 'where clause', query was: SELECT FLOOR((ROUND((e.min_price) * 1, 2)) / 100) + 1 AS range, COUNT(*) AS count FROM catalog_product_index_price AS e
    – Aatif
    May 10, 2021 at 5:39
0
 $objectManager = \Magento\Framework\App\ObjectManager::getInstance();
        /** @var \Magento\Catalog\Model\ResourceModel\Product\Collection $productCollection */
        $productCollection = $objectManager->create('Magento\Catalog\Model\ResourceModel\Product\Collection');    

        $min =10; $max =1000;

        $productCollection->addFieldToFilter('special_price', ['lt' => $min, 'gt' => $max]);

        $productCollection->load();
2
  • why the $min/$max limit? Jun 9, 2017 at 13:24
  • @YehiaA.Salam I know it not compulsory, just added for the limit
    – sandip
    Jun 9, 2017 at 16:00

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.