Magento 2.2 Price Sorting – Fix Incorrect Product Sorting by Price

magento2pricesorting

I'm using Magento CE 2.2.6
In front-end when we sort the category pages by price, the sorting happens wrongly.

Any suggestions are appreciated.

Best Answer

As I investigated this seems to be a Magento core issue. https://github.com/magento/magento2/issues/18264

The fix for this is provided in below link. https://github.com/magento/magento2/commit/14ab8ace12f0e95a476675548d0712d5ecaf9a26#diff-5a30a751305f50e9d7bbd8a2576a2b46

Fix:

Create a patch file in 'patches' folder inside your project directory and add the below content there.

--- /vendor/magento/module-catalog/Model/ResourceModel/Product/Indexer/Price/Query/BaseFinalPrice.php   2018-10-25 17:11:00.749047680 +0530
+++ /vendor/magento/module-catalog/Model/ResourceModel/Product/Indexer/Price/Query/BaseFinalPrice.php   2018-10-25 17:15:19.657517309 +0530
@@ -190,7 +190,7 @@
         $specialFromExpr = "{$specialFrom} IS NULL OR {$specialFromDate} <= {$currentDate}";
         $specialToExpr = "{$specialTo} IS NULL OR {$specialToDate} >= {$currentDate}";
         $specialPriceExpr = $connection->getCheckSql(
-            "{$specialPrice} IS NOT NULL AND {$specialFromExpr} AND {$specialToExpr}",
+            "{$specialPrice} IS NOT NULL AND ({$specialFromExpr}) AND ({$specialToExpr})",
             $specialPrice,
             $maxUnsignedBigint
         );

Once you create the patch file, then run a composer update (or install if you prefer) and then do a full reindex.

That's it. Hope this will help someone in need.

NOTE: I have done this with Magento CE 2.2.6

The Background:

As @Shawn Abramson mentioned, this is not possible without an additional mechanism.

The sorting must be specified in the first query (Mage_Eav_Model_Entity_Collection_Abstract:1045), as this is the result set that future queries add data to. I believe you need to always include a separate join to the price index table. This is unfortunate, as it is more records to search/load, reducing performance. That trade-off is up to you and your client.

The Mechanism:

$products = Mage::getResourceSingleton('catalog/product_collection');
$products->addAttributeToSelect('name');
$products->setPageSize(10);
$products->setCurPage(1);

$products->joinTable(
    ['price_table' => Mage::getResourceSingleton('core/resource')->getTable('catalog/product_index_price')],
    'entity_id = entity_id',
    [ 'price', 'final_price' ],
    [
        'customer_group_id' => 0,
        'website_id' => Mage::app()->getStore()->getWebsiteId()
    ]
);
$products->getSelect()->order('price_table.final_price DESC');

The Explanation:

The first four lines are whatever your standard collection filtering code is. Thereafter there are two functions:

$products->joinTable(): this function joins the catalog/product_index_price table and retrieve the two columns for reference. Please Note: you need specify the customer_group_id and the website_id unless these placeholder values work for you.
$products->getSelect()->order(): this function performs the order process.

Best Answer

Related Solutions

Magento – Change default sort order in Manage Products admin

Magento – Magento sort by price: sort by final price instead of price

The Background:

The Mechanism:

The Explanation:

Related Topic