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

before-pluginelasticsearchindexerproduct-sortingreindex

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

Best Answer

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

Related Solutions

Magento – Issue with Elastic search 6.x in Magento 2.3.3

Got across this several times and we came across this https://support.magento.com/hc/en-us/articles/360027356612-Elasticsearch-5-is-configured-but-search-page-does-not-load-with-Fielddata-is-disabled-error which not only identifies the problem but also gives a fix which works with several Magento versions. I tried 2.3.5 and 2.4.2.

By default, only certain types of product attributes can be used in Layered Navigation. They are Yes/No, Dropdown, Multipleselect, and Price. That is why in Magento Admin, you cannot set an attribute of any other type as Use in Layered Navigation = Filterable or Use in Search Results Layered Navigation = Yes. But there is a technical possibility to get around this limitation by directly changing the is_filterable and is_filterable_in_search values in the database. If this happens, and any other attribute type, like Date, Text, etc., is set to be used in Layered Navigation, Elasticsearch 5 (my edit: and others like ElasticSearch 6 and 7) throws an exception.

Fielddata is disabled on text fields by default. Set fielddata=true on [%attribute_code%]] in order to load fielddata in memory by uninverting the inverted index. Note that this can however use significant memory.

Step 1. Identify the product attributes which generate the issue using an SQL tool like phpmyadmin

SELECT ea.attribute_code, ea.frontend_input, cea.is_filterable, cea.is_filterable_in_search FROM eav_attribute AS ea
    INNER JOIN catalog_eav_attribute AS cea ON ea.attribute_id = cea.`attribute_id`
    WHERE (is_filterable = 1 OR is_filterable_in_search = 1) AND frontend_input NOT IN ('boolean', 'multiselect', 'select', 'price');

To fix the issue, you need to set is_filterable (that is, used in Layered Navigation) and filterable_in_search (that is, used in search results Layered Navigation) to "0" (not used).

UPDATE catalog_eav_attribute AS cea
    INNER JOIN eav_attribute AS ea
        ON ea.attribute_id = cea.attribute_id
SET cea.is_filterable = 0, cea.is_filterable_in_search = 0
WHERE (cea.is_filterable = 1 OR cea.is_filterable_in_search = 1) 
    AND frontend_input NOT IN ('boolean', 'multiselect', 'select', 'price');

then reindex all

bin/magento indexer:reindex

The fix says to reindex only catalogsearch_fulltext but this won't work if you have custom search modules since they use different indexes.

Magento 2.4.1 – Sort by Newest Not Working

I don't know this is the correct way to do this or not. I was waiting for someone to give a better answer but not getting one. So I am using this for now.

# Get the attribute_id of 'created_at'
select attribute_id from eav_attribute where attribute_code = 'created_at' and entity_type_id=4;

# Set frontend_label
update eav_attribute set frontend_label = 'Created At' where attribute_id=112;

# Set used_for_sort_by
update catalog_eav_attribute set used_for_sort_by = 1 where attribute_id=112;

Best Answer

Related Solutions

Magento – Issue with Elastic search 6.x in Magento 2.3.3

Magento 2.4.1 – Sort by Newest Not Working

Related Topic