Magento2: 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

[bruno-serfe]

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 [e-mail] 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 [e-mail] 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.