I’ve been trying to get a special price collection of products for Magento 2 but I keep on failing.
I’ve got the following example that works(almost):
protected function _getSaleCategoryCollection()
{
if ($this->_productCollection === null) {
/** @var MagentoCatalogModelResourceModelProductCollection $collection */
$collection = $this->_productCollectionFactory->create();
$this->_catalogLayer->prepareProductCollection($collection);
$todayStart = $this->_localeDate->date()->setTime(0, 0, 0)->format('Y-m-d H:i:s');
$todayEnd = $this->_localeDate->date()->setTime(23, 59, 59)->format('Y-m-d H:i:s');
$collection
->addStoreFilter()
->addAttributeToFilter(
'special_from_date',
[
'or' => [
['date' => true, 'to' => $todayEnd],
['is' => new Zend_Db_Expr('null')]
]
],
'left'
)->addAttributeToFilter(
'special_to_date',
[
'or' => [
['date' => true, 'from' => $todayStart],
['is' => new Zend_Db_Expr('null')]
]
],
'left'
)->addAttributeToFilter(
[
['attribute' => 'special_price', 'is' => new Zend_Db_Expr('not null')]
]
);
$this->_productCollection = $collection;
}
return $this->_productCollection;
}
}
Alright, so the above example shows me all the products that has the special price ‘value’ not empty and also if the product is between the 2 dates.
The problem comes when I have a product where the special price is not empty, but equal to the normal price(therefore the product does not have a special price).
So in conclusion I need to add an ‘and’ condition to the above collection so the final price < smaller price
I’ve added following after researching different forums:
...
)->addAttributeToFilter(
[
['attribute' => 'special_price', 'is' => new Zend_Db_Expr('not null')]
]
);
$collection->getSelect()->where(
'price_index.final_price < price_index.price'
);
$this->_productCollection = $collection;
....
After adding that getSelect->where()
, I’m only getting 4 simple products so I’m thinking this is not the right approach for me.
Also, I’m using elasticsearch(I don’t know if relevant).
I’d appreciate if anyone has an idea.
Thank you!