I have just upgraded to Magento 2.4.1 that REQUIRES you to use Elasticsearch as the search engine instead of MySql.

In the previous setup (Magento 2.3.2) I had a custom sort by a discount in the listing pages (a plugin that was overriding Toolbar.php):

public function aroundSetCollection(
    \Magento\Catalog\Block\Product\ProductList\Toolbar $subject,
    \Closure $proceed,
) {
    $currentOrder = $subject->getCurrentOrder();
    $result = $proceed($collection);
    $currentDirection = $subject->getCurrentDirection();

    if ($currentOrder) {
        if ($currentOrder == 'discount') {
            $nameExpr = new \Zend_Db_Expr('(`price_index`.`price`- `price_index`.`final_price`) / `price_index`.`price` as discount');
            $subject->getCollection()->getSelect()->order('discount DESC');

    return $result;

This worked really well, but since the upgrade the custom sorting does not do what it should and when I check the exception log I get this error:

main.CRITICAL: {"error":{"root_cause":[{"type":"query_shard_exception","reason":"No mapping found for [discount] in order to sort on","index_uuid":"BGhEoDllTtW8tOxJ7IJ8Ww","index":"andrei9000_product_1_v4"}],"type":"search_phase_execution_exception","reason":"all shards failed","phase":"query","grouped":true,"failed_shards":[{"shard":0,"index":"andrei9000_product_1_v4","node":"DGa694tHR0q9bh5qTUc55g","reason":{"type":"query_shard_exception","reason":"No mapping found for [discount] in order to sort on","index_uuid":"BGhEoDllTtW8tOxJ7IJ8Ww","index":"andrei9000_product_1_v4"}}]},"status":400} {"exception":"[object] (Elasticsearch\\Common\\Exceptions\\BadRequest400Exception(code: 400): {\"error\":{\"root_cause\":[{\"type\":\"query_shard_exception\",\"reason\":\"No mapping found for [discount] in order to sort on\",\"index_uuid\":\"BGhEoDllTtW8tOxJ7IJ8Ww\",\"index\":\"andrei9000_product_1_v4\"}],\"type\":\"search_phase_execution_exception\",\"reason\":\"all shards failed\",\"phase\":\"query\",\"grouped\":true,\"failed_shards\":[{\"shard\":0,\"index\":\"andrei9000_product_1_v4\",\"node\":\"DGa694tHR0q9bh5qTUc55g\",\"reason\":{\"type\":\"query_shard_exception\",\"reason\":\"No mapping found for [discount] in order to sort on\",\"index_uuid\":\"BGhEoDllTtW8tOxJ7IJ8Ww\",\"index\":\"andrei9000_product_1_v4\"}}]},\"status\":400} at /home/www/site/vendor/elasticsearch/elasticsearch/src/Elasticsearch/Connections/Connection.php:632)"} []

I just need a hint as to how I can map a virtual column so that Elastisearch can search by it


One solution that doesn't include manual operations on Elasticsearch, would be to save the discount on products in a custom attribute (but careful to make sure you are covering all ways that can change a product price in catalog), set the attribute to be used as a sorting criteria from the attribute's configuration page (the 'Used for Sorting in Product Listing' storefront property).

Otherwise, you would have to define a custom field in the product document mapping in Elasticsearch, then customly populate it with values whenever you are indexing the products (full or partial reindex to be both taken into account). This is, of course, the subject of a longer discovery and trial&error, definitely not straight-forward solution.

  • I ended up adding some empty attributes to stop Elasticsearch from breaking the page and using a custom mysql query to fetch the collection already filtered. Turns out that this actually saves me almost 1s of loading time on the page too. – Mircea Andrei Feb 10 at 7:21
  • that's interesting.. however, i would totally go with having the IDs already sorted when I query the page. but glad you find a solution! – Diana Botean Feb 10 at 14:15
  • Maybe I didn't make it very clear, sorry. I extended ListProduct.php and added a method that checked for that particular sorting and filtered the collection using Zend query and the collection is already filtered when it gets on the frontend. Hope that made it clear for anyone else who wants to try this approach. – Mircea Andrei Feb 12 at 6:18
  • yes, i think i got that from thte start. my point was that you are doing this sorting by Zend on your database, and not in ES where you could index the computed discount. so basically you end up hammering the database whenever you sort a page by discount, instead of just retrieving the product data based on the IDs from ES. know what i mean? i am not excluding your solution, it is a solution, but it seems to me a bit hacky (having empty attributes and sorting in magento). hope you don't mind me sticking to my idea :) – Diana Botean Feb 12 at 6:35
  • 1
    Now I understand what you were saying. Ended up changing the code again and adding the discount column to the index in ES. The only "hack" I had to do was adding the values to that field when indexing using another Zend query ^_^ But at least the sorting on front is now handled by ES as intended. Thanks again! – Mircea Andrei Feb 15 at 13:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.