Magento 2 how to use elasticsearch for custom indexer? Custom sort stops working after enabling elasticsearch

Question

I am a magento newbie and trying to implementing a custom sort on product listing page with the help of this: https://magento.stackexchange.com/a/230983/84727

On top of the solution i have built a custom indexer with assumption that elastic search would be keeping track of the index through magento but when selecting the option getting this error on debug log:

{"error":{"root_cause":[{"type":"query_shard_exception","reason":"No mapping found for [sale_count] in order to sort on","index_uuid":"bWC-SqwXSpijk9cpKLT7PA","index":"magento2_product_1_v4"}],"type":"search_phase_execution_exception","reason":"all shards failed" ,"phase":"query","grouped":true,"failed_shards":[{"shard":0,"index":"magento2_product_1_v4","node":"YrKI9XCaSwSEAB5KNGBSvw","reason":{"type":"query_shard_exception","reason":"No mapping found for [sale_count] in order to sort on","index_uuid":"bWC-SqwXSpijk9cpKLT7PA","index":"magento2_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 [sale_count] in order to sort on\",\"index_uuid\":\"bWC-SqwXSpijk9cpKLT7PA\",\"index\":\"m agento2_product_1_v4\"}],\"type\":\"search_phase_execution_exception\",\"reason\":\"all shards failed\",\"phase\":\"query\",\"grouped\":true,\"failed_shards\":[{\"shard\":0,\"index\":\"magento2_product_1_v4\",\"node\":\"YrKI9XCaSwSEAB5KNGBSvw\",\"reason\":{\"type\":\"query_shard_exception\",\"reason\":\"No mapping found for [sale_count] in order to sort on\",\"index_uuid\":\"bWC-SqwXSpijk9cpKLT7PA\",\"index\":\"magento2_product_1_v4\"}}]},\"status\":400} at /var/www/html/mg2/vendor/elasticsearch/elasticsearch/src/Elasticsearch/Connections/Connection.php:636)"}  

By this I understand that there is no mapping with the name "sale_count" but struggling to understand how to push it to elasticsearch.

Here is my indexer class function which is running fine and creating the appropriate data in my table.

/**
 * @param array $ids
 */
private function reindex($ids = [])
{
    $this->logger->info('Trying to run indexer');
    $select = $this->connection->select()
        ->from('sales_order_item', [
            'product_id' => 'product_id',
            'sale_count' => 'SUM(qty_ordered)'
        ])
        ->group('product_id');
    $this->connection->query(
        $this->connection->insertFromSelect($select, 'catalog_product_sale_count', [
            'product_id',
            'sale_count'
        ], \Magento\Framework\DB\Adapter\AdapterInterface::REPLACE)
    );
}

And for the sort handler I am using a beforePlugin like this:

class Bestsellers 
{
/**
 * Bestsellers sorting attribute
 */
const BESTSELLERS_SORT_BY = 'sale_count';

/**
 * @param Config $subject
 * @param $result
 * @return array
 */
public function afterGetAttributeUsedForSortByArray(Config $subject, $result)
{
    return array_merge($result, [self::BESTSELLERS_SORT_BY => __('Best Sellers')]);
}

/**
 * @param Toolbar $subject
 * @param Collection $collection
 */
public function beforeSetCollection(Toolbar $subject, Collection $collection)
{
    if ($subject->getCurrentOrder() == self::BESTSELLERS_SORT_BY) {
        $collection->getSelect()->joinLeft(
            'catalog_product_sale_count',
            'e.entity_id = catalog_product_sale_count.product_id',
            ['sale_count']
        )
            ->group('e.entity_id')
            ->order('catalog_product_sale_count.sale_count ' . $subject->getCurrentDirection());
    }
}
}

Need help to sort this thing out.

Thanks in advance

Answer 1

Custom Sort with ElasticSearch in Magento issue can be fixed by following these simple steps:

• Navigate to Stores->Attributes->Product in Admin.
• Add New Attribute.
• In Properties->Attribute Properties:
  • Add Default Label (Eg: Price: Low to High)
  • Select the Catalog Input Type for Store Owner (Eg: Price) (In other cases Text Field is enough).
• In Properties->Advanced Attribute Properties:
  • Add Attribute Code (Eg: price_asc).
• In Storefront Properties->Storefront Properties:
  • Select User for Sorting in Product Listing to Yes

Once done with the above steps run the following commands,

bin/magento indexer:reindex catalogsearch_fulltext

bin/magento indexer:reindex