magento2: Unable to sort catalog by Custom Attribute of type Dropdown

Preconditions and environment

2.4.5
Sample data

Steps to reproduce

To test this, we will create a test attribute and assign values to the products in the sample data category “Bag”, and then attempt to sort by the test attribute.

- Stores -> Attributes -> Product
 - Add New Attribute
  - Properties Tab
   - Default Label = test
   - Catalog Input Type for Store Owner = Dropdown
   - Add Option "first"
   - Add Option "second"
   - Add Option "third"
   - Add Option "fouth"

  - Storefront Properties Tab
   - Used for sorting in product listing = Yes
  - Leave all other options as default
 - Save Attribute

- Stores >Attributes -> Attribute Set
 - Select Bag
 - Drag test attribute into Product Details group
- Save

- Catalog -> Products -> Add filter to only show products in Attribute Set "Bag"
- Configure the following products to have test attribute values

Joust 24-MB01 - fourth
Fusion 24-MB02 - third
Crown Summit 24-MB03 - second
Strive 24-MB04 - first
Wayfarer 24-MB05 - fourth
Rival Field 24-MB06 - third

To make testing easier, remove all other products that have attribute set of “Bag” to leave only the above 6 items in the filtered view.

- Clear cache
- Go to frontend and browse to the Bag category.
- Sort by test

Expected result

The products should be ordered by test attribute. (Attribute value in brackets)

Strive (first)
Crown Summit (second)
Fusion (third)
Rival Field (third)
Joust (fourth)
Wayfarer (fourth)

Actual result

Additional information

No response

Release note

Products can now be sorted on the Product Listing pages by custom attribute of Dropdown type.

Triage and priority

Severity: S0 - Affects critical data or functionality and leaves users without workaround.
Severity: S1 - Affects critical data or functionality and forces users to employ a workaround.
Severity: S2 - Affects non-critical data or functionality and forces users to employ a workaround.
Severity: S3 - Affects non-critical data or functionality and does not force users to employ a workaround.
Severity: S4 - Affects aesthetics, professional look and feel, “quality” or “usability”.

About this issue

Original URL
State: closed
Created 2 years ago
Comments: 19 (7 by maintainers)

Most upvoted comments

Hello everybody,

I ran into the same problem. I think the root of this evil lies in \Magento\Elasticsearch\Model\ResourceModel\Fulltext\Collection\SearchResultApplier::categoryProductByCustomSortOrder. Here a completly new select statement is build which erases former table joins. Due to this circumstance custom sorting is not possible on category pages. Preconditions are that you are on a category page and you have enabled cataloginventory/options/show_out_of_stock.

I created a patch which fixes this behavior but can’t promise it’s a correct solution. Feedback is welcome:

Index: vendor/magento/module-elasticsearch/Model/ResourceModel/Fulltext/Collection/SearchResultApplier.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/vendor/magento/module-elasticsearch/Model/ResourceModel/Fulltext/Collection/SearchResultApplier.php b/vendor/magento/module-elasticsearch/Model/ResourceModel/Fulltext/Collection/SearchResultApplier.php
--- a/vendor/magento/module-elasticsearch/Model/ResourceModel/Fulltext/Collection/SearchResultApplier.php
+++ b/vendor/magento/module-elasticsearch/Model/ResourceModel/Fulltext/Collection/SearchResultApplier.php	(date 1662547508268)
@@ -213,17 +213,12 @@
         $sortOrders = array_merge(['is_salable' => \Magento\Framework\DB\Select::SQL_DESC], $sortOrders);
 
         $connection = $this->collection->getConnection();
-        $query = clone $connection->select()
+        $query = clone $this->collection->getSelect()
             ->reset(\Magento\Framework\DB\Select::ORDER)
             ->reset(\Magento\Framework\DB\Select::LIMIT_COUNT)
-            ->reset(\Magento\Framework\DB\Select::LIMIT_OFFSET)
-            ->reset(\Magento\Framework\DB\Select::COLUMNS);
-        $query->from(
-            ['e' => $this->collection->getTable('catalog_product_entity')],
-            ['e.entity_id']
-        );
+            ->reset(\Magento\Framework\DB\Select::LIMIT_OFFSET);
+
         $this->stockStatusApplier->setSearchResultApplier(true);
-        $query = $this->stockStatusFilter->execute($query, 'e', 'stockItem');
         $query->join(
             ['cat_index' => $this->collection->getTable('catalog_category_product_index_store' . $storeId)],
             'cat_index.product_id = e.entity_id'

codebase-md on Sep 7, 2022

Hi @gwharton ,

Thank you for providing inputs and we tried to reproduce the issue on Magento 2.4 develop , issue is reproducible. Actually products are getting sorted by its position instead of custom sort option.

Hence , issue has been confirmed.

Please find below screenshots for reference.

on backend setup:

On frontend Screenshot 2022-08-19 at 3 26 47 PM

engcom-Delta on Aug 19, 2022