Ttdfjt

Should I warn my boss I might take sick leave

What is meaning of 4 letter acronyms in Roman names like Titus Flavius T. f. T. n. Sabinus?

Can I have a forest in the rain shadow of a mountain range?

Cannot update a field to a Lookup, MasterDetail, or Hierarchy from something else (44:13)

Did Snape really give Umbridge a fake Veritaserum potion that Harry later pretended to drink?

A student "completes" 2-week project in 3 hours and lies about doing it himself

In National Velvet why didn't they use a stunt double for Elizabeth Taylor?

What could a Medieval society do with excess animal blood?

CPLEX exceeds time limit issue

Performance of loop vs expansion

Why did my leaking pool light trip the circuit breaker, but not the GFCI?

Do I need to be legally qualified to install a Hive smart thermostat?

Go function to test whether a file exists

Sleepy tired vs physically tired

Pandas merge and fillna

My mother co-signed for my car. Can she take it away from me if I am the one making car payments?

What is the right way to query an I2C device from an interrupt service routine?

Who are the police in Hong Kong?

Is there ever a reason not to use Java 8's parallelSort?

what is the meaning of "stock" dilution on the Massive Dev Chart Website?

What are the differences of checking a self-signed certificate vs ignore it?

Hiding a solar system in a nebula

When do I make my first save against the Web spell?

Does a reference have a storage location?

Magento 2 Catalog Search with Hyphen

How to get specific sku product on search in magento 2?What does the search function in Magento actually search for?Improving Magento search results (either via extension or free)Search results page shows all results on first pageManually specifying top search results for a specific search queryParticular search term not working in MagentoAfter saving product on frontend, search results disappearSearching for SKU without special charactersChange search query to lower case in catalog search results URLMagento 1.9.3.1 Quck Search Not workingMake Magento search more effective? Is there a way to make it search for product names first?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

2

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?

magento2 search catalogsearch

share|improve this question

asked Oct 18 '17 at 9:01

msblrmsblr

17366 bronze badges

add a comment | 

2

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?

magento2 search catalogsearch

share|improve this question

asked Oct 18 '17 at 9:01

msblrmsblr

17366 bronze badges

add a comment | 

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?

magento2 search catalogsearch

share|improve this question

asked Oct 18 '17 at 9:01

msblrmsblr

17366 bronze badges

share|improve this question

asked Oct 18 '17 at 9:01

msblrmsblr

17366 bronze badges

share|improve this question

asked Oct 18 '17 at 9:01

msblrmsblr

17366 bronze badges

asked Oct 18 '17 at 9:01

msblrmsblr

17366 bronze badges

asked Oct 18 '17 at 9:01

msblrmsblr

17366 bronze badges

1 Answer
1

active

oldest

votes

0

This is because the internal handling of the parameter by Magento 2 Query Builder. The "-" symbol is prohibited in search query (in MagentoFrameworkSearchAdapterMysqlQueryBuilderMatch 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?

share|improve this answer

answered Apr 19 '18 at 10:09

roman204roman204

76344 silver badges1515 bronze badges

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "479"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmagento.stackexchange.com%2fquestions%2f197721%2fmagento-2-catalog-search-with-hyphen%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

This is because the internal handling of the parameter by Magento 2 Query Builder. The "-" symbol is prohibited in search query (in MagentoFrameworkSearchAdapterMysqlQueryBuilderMatch 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?

share|improve this answer

answered Apr 19 '18 at 10:09

roman204roman204

76344 silver badges1515 bronze badges

add a comment | 

0

This is because the internal handling of the parameter by Magento 2 Query Builder. The "-" symbol is prohibited in search query (in MagentoFrameworkSearchAdapterMysqlQueryBuilderMatch 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?

share|improve this answer

answered Apr 19 '18 at 10:09

roman204roman204

76344 silver badges1515 bronze badges

add a comment | 

This is because the internal handling of the parameter by Magento 2 Query Builder. The "-" symbol is prohibited in search query (in MagentoFrameworkSearchAdapterMysqlQueryBuilderMatch 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?

share|improve this answer

answered Apr 19 '18 at 10:09

roman204roman204

76344 silver badges1515 bronze badges

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

share|improve this answer

answered Apr 19 '18 at 10:09

roman204roman204

76344 silver badges1515 bronze badges

answered Apr 19 '18 at 10:09

roman204roman204

76344 silver badges1515 bronze badges

answered Apr 19 '18 at 10:09

roman204roman204

76344 silver badges1515 bronze badges