Magento 2 function to get a list of products (simple and configurable) with special price on a given date

Question

I have the following function which gives me a list of simple products which are on special at the moment:

 public function getSpecialProducts($config = [])
    {
        /** @var $collection \Magento\Catalog\Model\ResourceModel\Product\Collection */
        $collection = $this->_productCollectionFactory->create();
        if(isset($config['categories'])){
            $collection->joinField(
                'category_id', $this->_resource->getTableName('catalog_category_product'), 'category_id', 
                'product_id = entity_id', null, 'left'
            )
            ->addAttributeToFilter('category_id', array(
                array('finset' => $config['categories']),
            ));
        }

        $collection->setVisibility($this->_catalogProductVisibility->getVisibleInCatalogIds())
        ->addAttributeToSelect('*')
        ->addStoreFilter()
        ->addMinimalPrice()
        ->addUrlRewrite()
        ->addTaxPercents()
        ->addFinalPrice();
        $collection->setPageSize(isset($config['pagesize'])?$config['pagesize']:5)
        ->setCurPage(isset($config['curpage'])?$config['curpage']:1)
        ->getSelect()->group("e.entity_id");
        $collection->getSelect()->where('price_index.final_price < price_index.price');
        return $collection; 

But it totally ignores the mother product if the child product is on special.

So I tried to use this function instead:

 public function getDealsProducts($config = [])
    {
        $todayStartOfDayDate = $this->_localeDate->date()->setTime(0, 0, 0)->format('Y-m-d H:i:s');
        $todayEndOfDayDate = $this->_localeDate->date()->setTime(23, 59, 59)->format('Y-m-d H:i:s');

        /** @var $collection \Magento\Catalog\Model\ResourceModel\Product\Collection */
        $collection = $this->_productCollectionFactory->create();
        if(isset($config['categories'])){
            $collection->joinField(
                'category_id', $this->_resource->getTableName('catalog_category_product'), 'category_id', 
                'product_id = entity_id', null, 'left'
            )
            ->addAttributeToFilter('category_id', array(
                array('finset' => $config['categories']),
            ));
        }
        $collection->setVisibility($this->_catalogProductVisibility->getVisibleInCatalogIds())
        ->addAttributeToSelect('*')
        ->addStoreFilter()->addAttributeToFilter(
            'special_from_date',
            [
            'or' => [
            0 => ['date' => true, 'to' => $todayEndOfDayDate],
            1 => ['is' => new \Zend_Db_Expr('null')],
            ]
            ],
            'left'
            )->addAttributeToFilter(
            'special_to_date',
            [
            'or' => [
            0 => ['date' => true, 'from' => $todayStartOfDayDate],
            1 => ['is' => new \Zend_Db_Expr('not null')],
            ]
            ],
            'left'
            )->addAttributeToFilter(
            [
            ['attribute' => 'special_from_date', 'is' => new \Zend_Db_Expr('not null')],
            ['attribute' => 'special_to_date', 'is' => new \Zend_Db_Expr('not null')],
            ]
            )->addAttributeToSort(
            'special_from_date',
            'desc'
            )
            ->setPageSize(isset($config['pagesize'])?$config['pagesize']:5)
            ->setCurPage(isset($config['curpage'])?$config['curpage']:1)
            ->getSelect()->group("e.entity_id");
            return $collection;
    }

But it is also not getting me to where I want to be.

I am new in magento 2. Please help

Answer 1

I didn't check your code, but I am sharing code which works for me:

In Block file:

    namespace Vendor\ModuleName\Block;

    use Magento\Catalog\Api\CategoryRepositoryInterface;

    class MyCollection extends \Magento\Catalog\Block\Product\ListProduct {

        protected $_collection;

        protected $categoryRepository;

        protected $_resource;

        public function __construct(
        \Magento\Catalog\Block\Product\Context $context, 
                \Magento\Framework\Data\Helper\PostHelper $postDataHelper, 
                \Magento\Catalog\Model\Layer\Resolver $layerResolver, 
                CategoryRepositoryInterface $categoryRepository,
                \Magento\Framework\Url\Helper\Data $urlHelper, 
                \Magento\Catalog\Model\ResourceModel\Product\Collection $collection, 
                \Magento\Framework\App\ResourceConnection $resource,
                array $data = []
        ) {
            $this->categoryRepository = $categoryRepository;
            $this->_collection = $collection;
            $this->_resource = $resource;

            parent::__construct($context, $postDataHelper, $layerResolver, $categoryRepository, $urlHelper, $data);
        }

        public function getProducts() {
            $count = $this->getProductCount();
            $category_id = $this->getData("category_id");
            $collection = clone $this->_collection;
            $collection->clear()->getSelect()->reset(\Magento\Framework\DB\Select::WHERE)->reset(\Magento\Framework\DB\Select::ORDER)->reset(\Magento\Framework\DB\Select::LIMIT_COUNT)->reset(\Magento\Framework\DB\Select::LIMIT_OFFSET)->reset(\Magento\Framework\DB\Select::GROUP);

            if(!$category_id) {
                $category_id = $this->_storeManager->getStore()->getRootCategoryId();
            }
            $category = $this->categoryRepository->get($category_id);
            $now = date('Y-m-d');
            if(isset($category) && $category) {
                $collection->addMinimalPrice()
                    ->addFinalPrice()
                    ->addTaxPercents()
                    ->addAttributeToSelect('name')
                    ->addAttributeToSelect('image')
                    ->addAttributeToSelect('small_image')
                    ->addAttributeToSelect('thumbnail')
                    ->addAttributeToSelect('special_from_date')
                    ->addAttributeToSelect('special_to_date')
                    ->addAttributeToFilter('special_price', ['neq' => ''])
                    ->addAttributeToFilter([
                        [
                            'attribute' => 'special_from_date',
                            'lteq' => date('Y-m-d G:i:s', strtotime($now)),
                            'date' => true,
                        ],
                        [
                            'attribute' => 'special_to_date',
                            'gteq' => date('Y-m-d G:i:s', strtotime($now)),
                            'date' => true,
                        ]
                    ])
                    ->addAttributeToFilter('is_saleable', 1, 'left');
            } else {
                $collection->addMinimalPrice()
                    ->addFinalPrice()
                    ->addTaxPercents()
                    ->addAttributeToSelect('name')
                    ->addAttributeToSelect('image')
                    ->addAttributeToSelect('small_image')
                    ->addAttributeToSelect('thumbnail')
                    ->addAttributeToFilter('special_price', ['neq' => ''])
                    ->addAttributeToSelect('special_from_date')
                    ->addAttributeToSelect('special_to_date')
                    ->addAttributeToFilter([
                        [
                            'attribute' => 'special_from_date',
                            'lteq' => date('Y-m-d G:i:s', strtotime($now)),
                            'date' => true,
                        ],
                        [
                            'attribute' => 'special_to_date',
                            'gteq' => date('Y-m-d G:i:s', strtotime($now)),
                            'date' => true,
                        ]
                    ])
                    ->addAttributeToFilter('is_saleable', 1, 'left');
            }

            $collection->getSelect()
                    ->limit($count);

            return $collection;
        }

        public function getLoadedProductCollection() {
            return $this->getProducts();
        }

        public function getProductCount() {
            $limit = $this->getData("product_count");
            if(!$limit)
                $limit = 10;
            return $limit;
        }
    }

In template file :

<?php foreach ($block->getLoadedProductCollection() as $product) : ?>
    <!-- do something with $product -->
<?php endforeach;?>