Magento – Magento 2 Catalog Search with Hyphen

catalogsearchmagento2search

All my skus have the following format: XY-NAME-NUMBER, e.g BC-FLUID-0003.

Now when I try to search that sku via catalog search in the frontend, I get all kinds of results, much more than I'd like to have.
I enabled the SQL-Query Log and found out, that Magento changes my sku in the search to "bc fluid* 0003*" and therefore finds products which are also named something with fluid or also end with 0003.

What I want is to search for BC-FLUID-0003*, so without taking away the hyphens.

How could I do that?

Best Answer

This is because the internal handling of the parameter by Magento 2 Query Builder. The "-" symbol is prohibited in a search query (in Magento\Framework\Search\Adapter\Mysql\Query\Builder\Match class). So it produces three small queries, in your case the final query looks similar to this:

SELECT `main_select`.`entity_id`, SUM(score) AS `relevance` FROM (SELECT DISTINCT  `eav_index`.`entity_id`, (((0)) * 1 + LEAST((MATCH (data_index) AGAINST ('bc fluid* 0003*' IN BOOLEAN MODE)), 1000000) * POW(2, search_weight)) AS `score` FROM `catalog_product_index_eav` AS `eav_index`
 INNER JOIN `catalogsearch_fulltext_scope1` AS `search_index` ON eav_index.entity_id = search_index.entity_id
 INNER JOIN `catalog_eav_attribute` AS `cea` ON search_index.attribute_id = cea.attribute_id
 INNER JOIN `cataloginventory_stock_status` AS `stock_index` ON stock_index.product_id = eav_index.entity_id AND `stock_index`.`website_id` = 0 AND `stock_index`.`stock_status` = 1 AND `stock_index`.`stock_id` = 1 WHERE (eav_index.store_id = '1') AND (`eav_index`.`attribute_id` = 96 AND `eav_index`.`value` in ('3', '4') AND `eav_index`.`store_id` = '1') AND (MATCH (data_index) AGAINST ('bc fluid* 0003* fn' IN BOOLEAN MODE))) AS `main_select` GROUP BY `entity_id` ORDER BY `relevance` DESC, `entity_id` DESC
 LIMIT 10000

To be able to find the product by SKU, I would suggest to make an around plugin like in this tread: How to get specific sku product on search in magento 2?

Related Topic