I have created functionality to show all the special price products on the product list page by extending the core class \Magento\Catalog\Model\Layer. This functionality is working fine with default database structure but, not working with Catalog flat table Below are the error showing on frontend:
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(*) AScount
FROMcatalog_product_index_price
ASe
INNER JOINcatalog_category_product_index_store3
AScat_index
ON cat_index.product_id=e.entity_id AND cat_index.store_id=3 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=2 INNER JOIN (SELECTproduct
.entity_id
ASentity_pk_value
,rt
.avg_percent
FROMcatalog_product_entity
ASproduct
LEFT JOIN (SELECTrova
.entity_pk_value
, avg(percent_approved) ASavg_percent
FROMrating_option_vote_aggregated
ASrova
WHERE (rova.store_id = 3) GROUP BYrova
.entity_pk_value
) ASrt
ON product.entity_id = rt.entity_pk_value GROUP BYproduct
.entity_id
) ASrt
ON e.entity_id = rt.entity_pk_value LEFT JOINreview_entity_summary
ASreview_summary
ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 3 AND review_summary.entity_type = (SELECTreview_entity
.entity_id
FROMreview_entity
WHERE (entity_code = 'product')) INNER JOINcataloginventory_stock_status
ASstock_status_index
ON e.entity_id = stock_status_index.product_id WHERE (((((e.special_from_date <= '2021-05-10 23:59:59') OR (e.special_from_date IS null))))) AND (((((e.special_to_date >= '2021-05-10 00:00:00') OR (e.special_to_date IS null))))) AND (e.special_price > '0.1') AND (e.price > '0.1') AND (stock_status_index.stock_status = 1) AND ( e.website_id = '1' ) AND ( e.customer_group_id = 0) AND (e.min_price IS NOT NULL) GROUP BY FLOOR((ROUND((e.min_price) * 1, 2)) / 100) + 1 ORDER BY (FLOOR((ROUND((e.min_price) * 1, 2)) / 100) + 1) ASC
Below is the code to filter the product collection:
namespace Vendor\Offer\Model;
use Magento\Catalog\Api\CategoryRepositoryInterface; use Magento\Catalog\Model\ResourceModel\Product\Attribute\CollectionFactory as AttributeCollectionFactory; use Magento\Catalog\Model\ResourceModel\Product\CollectionFactory;
class Layer extends \Magento\Catalog\Model\Layer { protected $productCollectionFactory;
public function __construct(
\Magento\Catalog\Model\Layer\ContextInterface $context,
\Magento\Catalog\Model\Layer\StateFactory $layerStateFactory,
AttributeCollectionFactory $attributeCollectionFactory,
\Magento\Catalog\Model\ResourceModel\Product $catalogProduct,
\Magento\Store\Model\StoreManagerInterface $storeManager,
\Magento\Framework\Registry $registry,
CategoryRepositoryInterface $categoryRepository,
CollectionFactory $productCollectionFactory,
array $data = []
) {
$this->productCollectionFactory = $productCollectionFactory;
parent::__construct(
$context,
$layerStateFactory,
$attributeCollectionFactory,
$catalogProduct,
$storeManager,
$registry,
$categoryRepository,
$data
);
}
public function getProductCollection()
{
if (isset($this->_productCollections['xigen_custom'])) {
$collection = $this->_productCollections['xigen_custom'];
} else {
$collection = $this->productCollectionFactory->create();
$date = new \Zend_Date();
$collection->addAttributeToFilter(
'special_from_date',
[
'or' => [
0 => [
'date' => true,
'to' => $date->get('YYYY-MM-dd') . ' 23:59:59'
],
1 => [
'is' => new \Zend_Db_Expr('null')
],
]
],
'left'
);
$collection->addAttributeToFilter(
'special_to_date',
[
'or' => [
0 => [
'date' => true,
'from' => $date->get('YYYY-MM-dd') . ' 00:00:00'
],
1 => [
'is' => new \Zend_Db_Expr('null')
],
]
],
'left'
);
$collection->addAttributeToFilter('special_price', ['gt' => '0.1']);
$collection->addAttributeToFilter('price', ['gt' => '0.1']);
// $collection->addAttributeToFilter('special_price', ['lt' => new \Zend_Db_Expr('at_price.value')]);
$this->prepareProductCollection($collection);
$this->_productCollections['xigen_custom'] = $collection;
}
return $collection;
}
}
Also, I change my approach to fix this issue, but still there some problem.
public function getProductCollection() {
if (isset($this->_productCollections['xigen_custom'])) {
$collection = $this->_productCollections['xigen_custom'];
} else {
$collection = $this->productCollectionFactory->create();
$date = new \Zend_Date();
$collection->addAttributeToSelect('*');
$priceExpression = $collection->isEnabledFlat() ? new \Zend_Db_Expr('e.special_price') : new \Zend_Db_Expr('at_price.value');
$collection->addAttributeToFilter('special_price', ['lt', $priceExpression]);
$collection->addAttributeToFilter('special_to_date', ['neq' => ''])
->addAttributeToFilter('special_from_date', ['neq' => '']);
$todayStartOnDate = $date->get('YYYY-MM-dd') . ' 00:00:00';
$collection->getSelect()->where(
(
"("
. "IF(at_special_to_date_default.special_from_date IS NOT NULL AND e.special_to_date IS NOT NULL,"
. " at_special_to_date_default.special_from_date<= '" . $todayStartOnDate . "' AND e.special_to_date >= '" . $todayStartOnDate . "',"
. "IF(at_special_to_date_default.special_from_date IS NOT NULL, at_special_to_date_default.special_from_date<= '" . $todayStartOnDate . "',"
. "IF(e.special_to_date IS NOT NULL, e.special_to_date >= '" . $todayStartOnDate . "','')"
. ")"
. ")"
. ")"
));
echo $collection->getSelect();exit;
$this->prepareProductCollection($collection);
$this->_productCollections['xigen_custom'] = $collection;
}
return $collection;
}
Please help me to fix thi sissue.