I'm working on category page, and try to sort the products collection by Special Price.
I've override setCollection()
of \Magento\Catalog\Block\Product\ProductList\Toolbar
And the result is the only sort for Normal price (not including Special Price):
So my solution is Left Join with catalog_product_entity_decimal
table (this table is using for store Special Price, also store Normal Price as well, 149.0000 is normal price, and 120.0000 is special price).
And I print the query, and got this:
SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `catalog_product_entity_decimal`.`value` AS `special_price` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id='127'
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
LEFT JOIN `catalog_product_entity_decimal` ON e.entity_id = catalog_product_entity_decimal.entity_id WHERE (attribute_id = 78 or attribute_id = 77) AND (catalog_product_entity_decimal.store_id = 0) GROUP BY `entity_id` ORDER BY `special_price` desc, `price` desc
But with this, the GROUP BY
affected before ORDER BY
, and ruin my result.
Its remove the 120.0000 and only show the 149.0000 in query result
If I remove the GROUP BY
, it will show both, but the Magento code will error with "Already exists Entity Id".
If there is any way to make GROUP BY
go after ORDER BY
? Or better way to solve the problems?
Thanks.
Used for Sorting in Product Listing
value to Yes in special_price attribute.this->_collection->setOrder('price', 'desc');
is working as expected. But on the listing page, it shows final_price instead of minimal_price. For the sorting, they use a minimal price. This is the reason why the front end got confused. So I create a plugin for Magento\Catalog\Model\Product and return MinimalPrice for afterGetSpecialPrice. That shows the correct value in the front end. Thanks for your help.