Quick search by SKU not working properly

[harimayco]

Preconditions

1. Magento CE 2.1.7
2. PHP 5.6.28
3. MySQL 5.6.35

Steps to reproduce

1. Create an item with SKU# 16-07
2. Do frontend quick search for 16-07

Expected result

1. Search returns product 16-07

Actual result

1. Search returns no products.

Sku attribute:

[Andrii-Deineha]

@harimayco Internal ticket MAGETWO-70101 was created.

[magento-engcom-team]

@harimayco, thank you for your report. We've created internal ticket(s) MAGETWO-70101 to track progress on the issue.

[p-bystritsky]

The problem is caused by splitting request "16-07" into two "16" and "07", because "-" symbol is prohibited in search query (in Magento\Framework\Search\Adapter\Mysql\Query\Builder\Match class). So we had two too short queries.

[khawaib]

I tested it in 2.2.2 and this problem still exists. SKU is VL-50 and when searched no results. Advanced search returns correct results when SKU is searched there.

[sakti-do]

@magento-engcom-team - I'm using Magento version 2.1.7, even I'm also facing same issue, in my case, there is a product with SKU "Test8632-D", but when I'm searching that with "8632" in front-end quick search , the product is not coming as result. But, if I search with "Test8632", then it's coming up with result

[khawaib]

Our tests show that issue with using hyphen(-) in SKU codes. Magento 2 has a bug that splits keywords with hyphens. We have decided to keep using Magento 1 for now as its more stable.

[langowskiadam]

distributed-cd

[magento-engcom-team]

@langowskiadam thank you for joining. Please accept team invitation here and self-assign the issue.

[langowskiadam]

Magento search require to provide always prefix (starting part) of search query, that's why it find Test8632, but not 8632. Magento search remove "-" and split query on parts. Each part must be longer then 2 chars. Unfortunately it not always working for 3 chars i.e. working for ccc, tes, but not for kkk. I attaching my catalogsearch_fulltext_scope1 data. catalogsearch_fulltext_scope1.txt https://stackoverflow.com/questions/5192499/how-to-allow-fulltext-searching-with-hyphens-in-the-search-query

[johnmpasq]

You could change the search logic from "or" (should) to and (must), this will give you the result you are looking for

[ghost]

Hi @johnmpasq I think you are referring to $conditionType right? I've tried that and I've the exactly same issue..

[robgt]

@johnmpasq That will change the search logic from OR to AND, but it doesn't resolve the issue of searching for SKU's containing a hyphen.

Is there a fix??

[johnmpasq]

Yes an extension was developed I have installed it and it works perfectly http://www.ibnab.com/en/blog/magento-2/magento-2-get-one-search-result-by-sku

On Wed, Jun 20, 2018 at 7:25 AM, robgt notifications@github.com wrote:

@johnmpasq https://github.com/johnmpasq That will change the search logic from OR to AND, but it doesn't resolve the issue of searching for SKU's containing a hyphen.

Is there a fix??

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/magento/magento2/issues/9988#issuecomment-398715921, or mute the thread https://github.com/notifications/unsubscribe-auth/AXYMtKe3vHcSfULlNljtGHX4BG12wJo0ks5t-jEWgaJpZM4N_GZ_ .

[magento-engcom-team]

Hi @engcom-backlog-nazar. Thank you for working on this issue. Looks like this issue is already verified and confirmed. But if your want to validate it one more time, please, go though the following instruction:

• [ ] 1. Add/Edit Component: XXXXX label(s) to the ticket, indicating the components it may be related to.

• [ ] 2. Verify that the issue is reproducible on 2.3-develop branch

  Details

  - Add the comment @magento-engcom-team give me 2.3-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.3-develop branch, please, add the label Reproduced on 2.3.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and stop verification process here!

• [ ] 3. Verify that the issue is reproducible on 2.2-develop branch.

  Details

  - Add the comment @magento-engcom-team give me 2.2-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.2-develop branch, please add the label Reproduced on 2.2.x

• [ ] 4. If the issue is not relevant or is not reproducible any more, feel free to close it.

[magento-engcom-team]

Hi @harimayco. Thank you for your report. The issue has been fixed in magento/magento2#20727 by @Nazar65 in 2.3-develop branch Related commit(s):

• cb681ab9f7f44457b420d7ae86cf7b324b7b8c94
• 7539e8e6829197467b59e417e317d38ea39a005b

The fix will be available with the upcoming 2.3.1 release.

[magento-engcom-team]

Hi @harimayco. Thank you for your report. The issue has been fixed in magento/magento2#20876 by @Nazar65 in 2.2-develop branch Related commit(s):

• 49d09cf331cd1ec6644b19ee7666ccfd87500c38

The fix will be available with the upcoming 2.2.9 release.

[mpurish]

Hi,

I have Magento 2.3.1 installed and this issue still exists, even with all the updates from related commits present.

[AndyJAllen]

Hi,

I have Magento 2.3.1 installed and this issue still exists, even with all the updates from related commits present.

I can confirm this behavior happens in 2.3.1

[Nazar65]

@AndyJAllen https://github.com/magento/magento2/issues/9988#issuecomment-378644539

[AndyJAllen]

@Nazar65 https://github.com/magento/magento2/issues/9988#issuecomment-398715921

[Nazar65]

@AndyJAllen well this can be done with mysql config ft_min_word_len=2, the default value for this config 3, in this case dashes is prohibited and splitef to 2 query's xx and xx and mysql doesn't search for 2 words

[mpurish]

@AndyJAllen Here's a ticket I've opened last month, but it's probably not solved yet. https://github.com/magento/magento2/issues/23447

[Nazar65]

@mpurish i don't think that this can be done here's. As for case if sku like xx-gg you can use config mentioned above, but if you have skus like x-xx then you will have same issue, and dash must be also included in search.

[mpurish]

@Nazar65 I have SKUs like xx-xx. Changing search logic to AND doesn't solve the issue and I'm not very keen on updating ft_min_word_len in database, since it might mess things up with next Magento update.

[mpurish]

@Nazar65 Sorry, I thought that ft_min_word_len is a field in database and not a database config property. Actually tried adding ft_min_word_len=2 to my.cnf file on server, reindexed the catalog, but still can't search using my xx-xx SKUs.

[Nazar65]

@mpurish you need to restart mysql, more information in this ticket https://github.com/magento/magento2/issues/21059#issuecomment-463122053

[mpurish]

@Nazar65 Thank you, it works now! Дякую, Назаре!

[AndyJAllen]

You could change the search logic from "or" (should) to and (must), this will give you the result you are looking for

Is there a Magento setting for this or do we have to modify code?

edit: found the setting in module-catalog-search/etc/search_request.xml

[johnmpasq]

You could change the search logic from "or" (should) to and (must), this will give you the result you are looking for

Is there a Magento setting for this or do we have to modify code?

edit: found the setting in module-catalog-search/etc/search_request.xml

I endeded up switching to elasticsearch but before I used an extension http://www.ibnab.com/en/blog/magento-2/magento-2-get-one-search-result-by-sku that fixed the problem. I know it works on 2.3.1

[AndyJAllen]

You could change the search logic from "or" (should) to and (must), this will give you the result you are looking for

Is there a Magento setting for this or do we have to modify code? edit: found the setting in module-catalog-search/etc/search_request.xml

I endeded up switching to elasticsearch but before I used an extension http://www.ibnab.com/en/blog/magento-2/magento-2-get-one-search-result-by-sku that fixed the problem. I know it works on 2.3.1

Unless you have less than 2 characters in between hyphens, I had success changing MySQL's my.cnf file and Magento's search_request.xml file. I'm also on 2.3.1.

In my.cnf add: innodb_ft_min_token_size=2 ft_min_word_len=2

In search_request.xml change: <queryReference clause="should" ref="search" /> to <queryReference clause="must" ref="search" />

Restart mysqld service and reindex catalog.

I tried the elasticsearch; and that software is a pain in the arse. You also have to pay for their gold or higher membership to implement security to lock searches down with credentials. It also wasn't compatible with the extension I use for search auto completion. I'm just going to stick to MySQL searching for now and probably avoid upgrading Magento when they decide to permanently phase it out. My search performance is plenty fast.

[johnmpasq]

Thats why I switched now. DIdn't want the hassle of switching when they dump it. You got it working though thats good.

On Thu, Jul 18, 2019 at 7:55 AM Andy notifications@github.com wrote:

You could change the search logic from "or" (should) to and (must), this will give you the result you are looking for

Is there a Magento setting for this or do we have to modify code? edit: found the setting in module-catalog-search/etc/search_request.xml

I endeded up switching to elasticsearch but before I used an extension http://www.ibnab.com/en/blog/magento-2/magento-2-get-one-search-result-by-sku that fixed the problem. I know it works on 2.3.1 Unless you have less than 2 characters in between hyphens, I had success changing MySQL's my.cnf file and Magento's search_request.xml file.

In my.cnf add: innodb_ft_min_token_size=2 ft_min_word_len=2

In search_request.xml change:

to

Restart mysqld service and reindex catalog.

I tried the elasticsearch; and that software is a pain in the arse. You also have to pay for their gold or higher membership to implement security to lock searches down with credentials. It also wasn't compatible with the extension I use for search auto completion. I'm just going to stick to MySQL searching for now and probably avoid upgrading Magento when they decide to permanently phase it out.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/magento/magento2/issues/9988?email_source=notifications&email_token=AF3AZNCGLAPFWCNDIGQNXTLQABK2LA5CNFSM4DP4MZ72YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD2IHN5Y#issuecomment-512784119, or mute the thread https://github.com/notifications/unsubscribe-auth/AF3AZNFOKFVAD5PCFH5FZRTQABK2LANCNFSM4DP4MZ7Q .

[gpaluk]

Why has this been closed? I can confirm that this issue or very similar issue is still happening in the latest release build 2.3.2. When using the following search filter (The implementation is described in the official documentation).

this.find('sku', 'door_', 'like')

async find(field: string, contains: string, condition: string, pageSize: number = 10, currentPage: number = 1) {
        try {
            let query: string = `
            searchCriteria[filter_groups][0][filters][0][field]=${field}&
            searchCriteria[filter_groups][0][filters][0][value]=${contains}&
            searchCriteria[filter_groups][0][filters][0][condition_type]=${condition}`

            await Backend.Api.get(`products?${query}`)
        }
        catch (error) {
            console.warn("An error occurred: " + error)
        }
    }

which would call to the API as:

GET <host>/rest/<store_code>/V1/products?
searchCriteria[filter_groups][0][filters][0][field]=sku&
searchCriteria[filter_groups][0][filters][0][value]=door_&
searchCriteria[filter_groups][0][filters][0][condition_type]=like

This should return products, but the result is an empty dataset.

[shweta2014]

I am still getting this issue in 2.2.7. In my case when I am searching the product with keyword "A-9" in quick search at the frontend, It's not showing any result. I have specified the string "A-9" in both Product Name and SKU.
But It's showing the result when I am searching in the SKU field with Advance Search.

[Nazar65]

@shweta2014 that is the reason why this issue closed, this feature available on ee version, and i don't know if this will be correct to do same thing on ce version.

[tushhan]

I have upgraded to the lastest Magento 2.3.4, the issue is still not resolved. I also installed http://www.ibnab.com/en/blog/magento-2/magento-2-get-one-search-result-by-sku but it didnt resolve the issue. Our SKUs patterns are X-1234 and XXX-1234.

[phuongtieugia]

I have same issue on magento 2.3.1. My SKU is z-70

[vandijkstef]

Having the same issue on 2.3.7. Internally and by resellers the codes are used A LOT. 2.4.X is not an option since our hosting company isn't supporting elastic search yet.

The problem is caused by splitting request "16-07" into two "16" and "07", because "-" symbol is prohibited in search query (in Magento\Framework\Search\Adapter\Mysql\Query\Builder\Match class). So we had two too short queries.

Why is "-" prohibited? I understand some characters can have special meaning (like -excluding, or "specific" etc) but can't we change that pattern to " -"? (including a whitespace character)

We use a variety of patterns (X for letter, # for numbers): X-## XX-## XXX-## XX-XX-# XXXX-# ... to list a few

[ezerway]

The issue still happen in v2.4.1 with elasticsearch please re-open again. :((

[Nico2P]

The issue still here in v2.4.3 with elasticsearch

[mbautista]

Hello, This issue is still present in 2023 with Magento 2.4.3 and ElasticSearch7 : when I search for a sku containing a hyphen, say "abc-def", elastic search will search for "abc" or "def" : The product appears in the result, but after other products, even if I raise the score for the sku attribute.

I have tried to override the search_request.xml to change the "or" logic by "and", like explained here : https://community.magento.com/t5/Magento-2-x-Programming/How-do-I-change-magento-2-search-from-OR-logic-to-AND/td-p/496792 I only copied the xml file to my module and replaced <queryReference clause="should" ref="search" />) by <queryReference clause="must" ref="search" /> but then I have no result at all with "abc-def".

Stack overflow suggests applying a patch, but it seems that the patch is only for the Entreprise (not Open Source) Edition. Is this right ? https://magento.stackexchange.com/questions/365932/searching-for-product-with-dash-doesnt-work-in-magento-2

Please could anyone give a simple and complete hint about how to solve this ? Thank you very much.

[mbautista]

Hello, I have tried another 2 solutions, but none of them work :

1. install the patch tool and apply the patch, but it's not available for Magento 2.4.3, whereas the bug is still present...

   $ composer require magento/quality-patches
   ...
   $ ./vendor/bin/magento-patches apply MDVA-28993
   Magento 2 Community Edition, version 2.4.3.0-patch1
   Next patches weren't found: MDVA-28993. Please, check with "status" command availability of these patches for the current Magento version.

2. Try to add a "raw" sku field as suggested here : https://stackoverflow.com/questions/31301849/elasticsearch-searching-with-hyphen-inside-a-word But I could not figure how to do it, I have tried this in my search_request.xml :

   ...
           <query xsi:type="boolQuery" name="quick_search_container" boost="1">
               <queryReference clause="must" ref="search" />
               <queryReference clause="should" ref="partial_search" />
               <queryReference clause="must" ref="category"/>
               <queryReference clause="must" ref="price"/>
               <queryReference clause="must" ref="visibility"/>
           </query>
           <query xsi:type="matchQuery" value="$search_term$" name="search">
               <match field="sku.raw"/>
               <match field="*"/>
           </query>
   ...

   But with no luck... Does anyone have any suggestion please ? Thank you very much :)

EDIT : Concerning the patch, it's already applied in 2.4.3 that's why I can't apply it, but it does not solve the issue... Here is the source code of the patch : https://github.com/magento/quality-patches/blob/master/patches/os/MDVA-28993_2.3.4.patch

[mbautista]

Hello, Could anyone help with this please ?

It's really simple to reproduce : search a SKU with hyphen => elastic search gives lots of results instead of just the product

This bug happens at least until Magento 2.4.3 and maybe above : https://magento.stackexchange.com/questions/365932/searching-for-product-with-dash-doesnt-work-in-magento-2/368443#368443

It's a really critical bug... Thank you for your help.

[mbautista]

Hello, I think I found a solution to search products by SKU with hyphens (works for Magento 2.4.3) :

1. Copy vendor/magento/module-catalog-search/etc/search_request.xml to your module etc/search_request.xml

2. You can remove the request tags for advanced_search_container and catalog_view_container (keep only the quick_search_container)

3. Comment the "partial_search" query and queryReference tags

4. Add the <match field="sku.keyword"/> to the "search" query tag

Here the full search_request.xml file, please do not hesitate to share if you find a better solution/config :

<?xml version="1.0"?>
<!--
/**
 * Copyright © Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */
-->
<requests xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi:noNamespaceSchemaLocation="urn:magento:framework:Search/etc/search_request.xsd">
    <request query="quick_search_container" index="catalogsearch_fulltext">
        <dimensions>
            <dimension name="scope" value="default"/>
        </dimensions>
        <queries>
            <query xsi:type="boolQuery" name="quick_search_container" boost="1">
                <queryReference clause="should" ref="search" />
                <!--<queryReference clause="should" ref="partial_search" />-->
                <queryReference clause="must" ref="category"/>
                <queryReference clause="must" ref="price"/>
                <queryReference clause="must" ref="visibility"/>
            </query>
            <query xsi:type="matchQuery" value="$search_term$" name="search">
                <match field="*"/>
                <match field="sku.keyword"/>
            </query>
            <!--<query xsi:type="matchQuery" value="$search_term$" name="partial_search">
                <match field="*"/>
                <match field="name" matchCondition="match_phrase_prefix"/>
                <match field="sku" matchCondition="match_phrase_prefix"/>
            </query>-->
            <query xsi:type="filteredQuery" name="category">
                <filterReference clause="must" ref="category_filter"/>
            </query>
            <query xsi:type="filteredQuery" name="price">
                <filterReference clause="must" ref="price_filter"/>
            </query>
            <query xsi:type="filteredQuery" name="visibility">
                <filterReference clause="must" ref="visibility_filter"/>
            </query>
        </queries>
        <filters>
            <filter xsi:type="termFilter" name="category_filter" field="category_ids" value="$category_ids$"/>
            <filter xsi:type="rangeFilter" name="price_filter" field="price" from="$price.from$" to="$price.to$"/>
            <filter xsi:type="termFilter" name="visibility_filter" field="visibility" value="$visibility$"/>
        </filters>
        <aggregations>
            <bucket name="price_bucket" field="price" xsi:type="dynamicBucket" method="$price_dynamic_algorithm$">
                <metrics>
                    <metric type="count"/>
                </metrics>
            </bucket>
            <bucket name="category_bucket" field="category_ids" xsi:type="termBucket">
                <metrics>
                    <metric type="count"/>
                </metrics>
            </bucket>
        </aggregations>
        <from>0</from>
        <size>10000</size>
    </request>
</requests>

[MahetabSaiyed]

Hello @mbautista I try your last solution about to change the code of search_request.xml file but that not work in my Magento 2.4.5-p1

we need something this kind of search result like ex. product have SKU is "ML1797", so If search in search box exact "ML1797" it shows product but if I search "1797" then it does not show any product. I want to search like this

[mbautista]

Hello, It quite works for me on Magento 2.4.3, but Elastic Search still returns lots of results, so I had to install another module https://github.com/mooore-digital/magento2-module-elasticsearch-relevance and set a min score to 25-30. With that I manage to return the product and sometimes 2-3 other results when I type a SKU.

I'm sorry for now I couldn't find a better solution :(

The customer is not really happy with Magento 2 search, it was better on Magento 1, and we are looking for a payed plugin. This is really not cool, Magento should really work on providing an efficient out of the box search engine.

[samuel-lexim]

Got the same error in M2.4.6. Search by sku: Bundle-170-171-50780.