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?


This is because the internal handling of the parameter by Magento 2 Query Builder. The "-" symbol is prohibited in search query (in Magento\Framework\Search\Adapter\Mysql\Query\Builder\Match class). So it produces three small queries, in your case the final query look 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 a around plugin like it that tread: How to get specific sku product on search in magento 2?

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.