When you order by price it doesn't work

[NachoVidalGolosinas]

Preconditions (*)

Magento version 2.4 PHP version 7.4

Steps to reproduce (*)

Go to category and order by price

Expected result (*)

It is expected to order by price, leaving the products out of stock at the end

Actual result (*)

Sort by price without considering out of stock products

[tuyennn]

The fix delivered on https://github.com/tuyennn/magento2-outofstockatlast/pull/11 and release tag v1.0.2

[evolbug]

Hello, I have tested this on Magento 2.4.3 with Elasticsearch 7 and the issue still persists. Magento fulltext collections still seem to override whatever extra sorting is applied when the price filter is enabled

[CUTTRHOAT69]

Hello everyone, i created a patch to workaround this issue, migth help someone

Basically, i changed the file vendor/magento/module-elasticsearch/Model/ResourceModel/Fulltext/Collection/SearchResultApplier.php that apply the sort order for the price, adding an sort order for the stock status.

I guess the bug occurs because Magento add a direct sort order on the mysql SELECT, that cause of overwriting the module sort order (that is not on the mysql SELECT).

I don't know if the code is on the best practices, but might help someone (please tell me if you have some improves on this patch)

Thanks!

From 8924a76ba84d44e9df43edba32dec5f62f753891 Mon Sep 17 00:00:00 2001
From: Manerson <heliton@hibrido.com.br>
Date: Tue, 14 Dec 2021 14:42:20 -0300
Subject: [PATCH] patch

---
 .../Fulltext/Collection/SearchResultApplier.php           | 8 ++++++++
 1 file changed, 8 insertions(+)

diff --git a/vendor/magento/module-elasticsearch/Model/ResourceModel/Fulltext/Collection/SearchResultApplier.php b/vendor/magento/module-elasticsearch/Model/ResourceModel/Fulltext/Collection/SearchResultApplier.php
index 54b8c1966..c61300872 100644
--- a/vendor/magento/module-elasticsearch/Model/ResourceModel/Fulltext/Collection/SearchResultApplier.php
+++ b/vendor/magento/module-elasticsearch/Model/ResourceModel/Fulltext/Collection/SearchResultApplier.php
@@ -75,6 +75,14 @@ class SearchResultApplier implements SearchResultApplierInterface
         $sortOrder = $this->searchResult->getSearchCriteria()
             ->getSortOrders();
         if (!empty($sortOrder['price']) && $this->collection->getLimitationFilters()->isUsingPriceIndex()) {
+            $websiteId = 0;
+            $this->collection->getSelect()->joinLeft(
+                array('_inv' => $this->collection->getResource()->getTable('cataloginventory_stock_status')),
+                "_inv.product_id = e.entity_id and _inv.website_id=$websiteId",
+                array('stock_status')
+            );
+            $this->collection->addExpressionAttributeToSelect('in_stock', 'IFNULL(_inv.stock_status,0)', array());
+            $this->collection->getSelect()->order('in_stock DESC');
             $sortDirection = $sortOrder['price'];
             $this->collection->getSelect()
                 ->order(
-- 
2.25.1

[i6media]

This issue has been resolved by Magento 2.4.5:

https://github.com/magento/magento2/commit/784b94326433ab6be44c89db6185cbe9f4b2ca12?diff=unified#diff-1bda4b8d76362780cc546ecd1550493ce8030bea00298543a08831b61022a32d

[tuyennn]

@i6media appreciate for the updates will close this ticket due internal magento bug MC-42243

[tuyennn]

Thanks @NachoVidalGolosinas @i6media @CUTTRHOAT69 The bug has been fixed on https://github.com/tuyennn/magento2-outofstockatlast/pull/21 And release tag v1.0.4