Search facility on partial part numbers - malfunction

[keat]

I know i've covered this before, just don't recall the outcome.

 

If a customer searches a partial part number, then nothing shows up in the search results.

eg: We sell hose clips with a part number of HC10, HC12, HC14 etc, if he searches HC, then no products are found.

We also sell stainless steel ones with a part number of SSHC10, SSHC12, SSHC14 etc.

However, In the product description for our standard HC range is a phrase "for stainless steel hose clips consider our SSHC range"

When the customer did a partial part number search for SSHC, all the clips with the above phrase appeared in the search results, but none of the actual SSHC ones.

Resulting in the customer purchasing the wrong ones.

 

Are there any fixes to the search facility, or are there any mods/plugins which would help alleviate this problem.

 

[Noodleman]

It's been on the to do list to overhaul for a while.

I've overhauled it for a couple of my customers who wanted some other types of behaviour so it is possible. a search for "HC" will using a different method than longer searched. I think it's either 3 or 4 characters or more uses full text indexes in the DB, where as shorter ones use a more traditional LIKE SQL search.

If your technical, enable debug mode, perform your searches and capture the SQL queries, it's a good first step in understanding how it's searching for your keyword, or words. Those queries can then be manipulated in the core code to better suit your needs.

 

 

 

[keat]

I'm not sure how much of the debug is relevent.

However, here is the section showing sshc as my search.

 

[1] SELECT SQL_CALC_FOUND_ROWS * FROM `CubeCart_sessions` WHERE CubeCart_sessions.session_id = 'af5050130d5820475c332101b35681c2' LIMIT 1; -- (0.000498056411743 sec) [NOT CACHED]
[2] SELECT FOUND_ROWS() as Count; -- (0.000113964080811 sec) [NOT CACHED]
[3] SELECT * FROM `CubeCart_hooks` WHERE CubeCart_hooks.enabled = '1' ORDER BY priority ASC ; -- (0.000113964080811 sec) [CACHE READ]
[4] SELECT `php_code`, `unique_id`, `description`, `hook_trigger` FROM `CubeCart_code_snippet` WHERE CubeCart_code_snippet.enabled = '1' ORDER BY `priority` ASC ; -- (0.000113964080811 sec) [CACHE READ]
[5] SHOW COLUMNS FROM CubeCart_seo_urls; -- (0.0010449886322 sec) [CACHE WRITE]
[6] SELECT * FROM `CubeCart_seo_urls` WHERE CubeCart_seo_urls.path = 'search' ; -- (0.000403881072998 sec) [CACHE WRITE]
[7] SELECT SQL_CALC_FOUND_ROWS `array` FROM `CubeCart_config` WHERE CubeCart_config.name = 'logos' LIMIT 1; -- (0.000403881072998 sec) [CACHE READ]
[8] SELECT SQL_CALC_FOUND_ROWS `status`, `countries` FROM `CubeCart_modules` WHERE CubeCart_modules.folder = 'logos' LIMIT 1; -- (0.000343084335327 sec) [CACHE WRITE]
[9] SELECT `folder` FROM `CubeCart_modules` WHERE CubeCart_modules.module = 'livehelp' AND CubeCart_modules.status = '1' ; -- (0.000221967697144 sec) [CACHE WRITE]
[10] SELECT * FROM `CubeCart_currency` WHERE CubeCart_currency.code = 'GBP' ; -- (0.000221967697144 sec) [CACHE READ]
[11] SELECT SQL_CALC_FOUND_ROWS `array` FROM `CubeCart_config` WHERE CubeCart_config.name = 'ccss' LIMIT 1; -- (0.000221967697144 sec) [CACHE READ]
[12] SELECT SQL_CALC_FOUND_ROWS `status`, `countries` FROM `CubeCart_modules` WHERE CubeCart_modules.folder = 'ccss' LIMIT 1; -- (0.000221967697144 sec) [CACHE READ]
[13] SELECT `ip_address` FROM `CubeCart_ccss_ip_addresses` WHERE CubeCart_ccss_ip_addresses.trusted = '0' ; -- (0.000365972518921 sec) [CACHE WRITE]
[14] SELECT `id`, `hits` FROM `CubeCart_search` WHERE CubeCart_search.searchstr = 'SSHC' ; -- (0.00797319412231 sec) [CACHE WRITE]
[15] UPDATE `CubeCart_search` SET `hits` = '363' WHERE CubeCart_search.id = '2730'; -- (0.000272989273071 sec) [NOT CACHED]
[16] SHOW INDEX FROM `CubeCart_inventory`; -- (0.000272989273071 sec) [CACHE READ]
[17] SHOW VARIABLES LIKE 'ft_min_word_len' -- (0.000272989273071 sec) [CACHE READ]
[18] SELECT I.*, MATCH (I.product_code,I.description,I.name) AGAINST('sshc' IN BOOLEAN MODE) AS Relevance FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (MATCH (I.product_code,I.description,I.name) AGAINST('sshc' IN BOOLEAN MODE)) >= 0.5 ORDER BY Relevance DESC LIMIT 12 OFFSET 0 -- (0.00169110298157 sec) [CACHE WRITE]
[19] SELECT COUNT(I.product_id) as count, MATCH (I.product_code,I.description,I.name) AGAINST('sshc' IN BOOLEAN MODE) AS Relevance FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (MATCH (I.product_code,I.description,I.name) AGAINST('sshc' IN BOOLEAN MODE)) >= 0.5 GROUP BY I.product_id ORDER BY Relevance DESC -- (0.000794887542725 sec) [CACHE WRITE]
[20] SELECT * FROM `CubeCart_lang_strings` WHERE CubeCart_lang_strings.language = 'en-GB' ; -- (0.000794887542725 sec) [CACHE READ]
[21] SELECT SQL_CALC_FOUND_ROWS `array` FROM `CubeCart_config` WHERE CubeCart_config.name = 'category_product_options' LIMIT 1; -- (0.000794887542725 sec) [CACHE READ]
[22] SELECT SQL_CALC_FOUND_ROWS `status`, `countries` FROM `CubeCart_modules` WHERE CubeCart_modules.folder = 'category_product_options' LIMIT 1; -- (0.000291109085083 sec) [CACHE WRITE]
[23] SELECT FOUND_ROWS() as Count; -- (8.89301300049E-5 sec) [NOT CACHED]
[24] SHOW COLUMNS FROM CubeCart_options_set_product; -- (0.000616073608398 sec) [CACHE WRITE]
[25] SELECT `set_id` FROM `CubeCart_options_set_product` WHERE CubeCart_options_set_product.product_id = '2311' ; -- (0.000251054763794 sec) [CACHE WRITE]
[26] SHOW COLUMNS FROM CubeCart_option_assign; -- (0.000720977783203 sec) [CACHE WRITE]
[27] SELECT * FROM `CubeCart_option_assign` WHERE CubeCart_option_assign.product = '2311' AND CubeCart_option_assign.set_member_id = '0' AND CubeCart_option_assign.set_enabled = '1' ; -- (0.000684976577759 sec) [CACHE WRITE]
[28] SELECT `set_id` FROM `CubeCart_options_set_product` WHERE CubeCart_options_set_product.product_id = '2312' ; -- (0.000241994857788 sec) [CACHE WRITE]
[29] SELECT * FROM `CubeCart_option_assign` WHERE CubeCart_option_assign.product = '2312' AND CubeCart_option_assign.set_member_id = '0' AND CubeCart_option_assign.set_enabled = '1' ; -- (0.000649213790894 sec) [CACHE WRITE]
[30] SELECT `quantity`, `price` FROM `CubeCart_pricing_quantity` WHERE CubeCart_pricing_quantity.product_id = '2311' AND CubeCart_pricing_quantity.group_id = '0' ORDER BY `quantity` ASC, `price` ASC ; -- (0.000317096710205 sec) [CACHE WRITE]
[31] SELECT SQL_CALC_FOUND_ROWS * FROM `CubeCart_image_index` WHERE CubeCart_image_index.product_id = '2311' ORDER BY `main_img` DESC LIMIT 1; -- (0.000247001647949 sec) [CACHE WRITE]

Icidentally, I tidied up the products this morning, so the phrase  "for stainless steel hose clips consider our SSHC range" no longer exists.

This at least in the short term will stop someone from finding the wrong product.

[Noodleman]

It's doing a full text search using this select query

SELECT I.*, MATCH (I.product_code,I.description,I.name) AGAINST('sshc' IN BOOLEAN MODE) AS Relevance FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (MATCH (I.product_code,I.description,I.name) AGAINST('sshc' IN BOOLEAN MODE)) >= 0.5 ORDER BY Relevance DESC LIMIT 12 OFFSET 0

 

The trick is to now re-work that query, or totally re-write it to get you the results you want. Once you know what your query needs to be updating the code to make it work like that is a lot easier.

[keat]

I've no idea how to write this in php, but an SQL query along the lines

SELECT *
FROM `CubeCart_inventory`
WHERE `product_code` LIKE '%sshc%'
OR `description` LIKE '%sshc%'
OR `name` LIKE '%sshc%'
 

seems to give better results

 

 

[Dirty Butter]

2 hours ago, keat said:

"for stainless steel hose clips consider our SSHC range"

I know you removed that phrase - but are all those clips in a specific Category/Subcategory? If so, you might be able to put your reference to that range as a link to that category/subfolder OR if a specific search method can be coded as Noodleman is suggesting - without using the SSHC in the link.

[keat]

How does the search from the admin side work ??

If I search products on the admin side of the cart and put for instance SSHC, i'm presented with a drop down list of all SSHC's

If i search 'Stainless', I'm presented with a drop down showing everything in stainless.

It seems that the admin side works just fine but it doesn't search the product description.

 

 

mmmm maybe not...... if I search 'TR' which is another common product code prefix, then it comes back with a million items

 

 

 

 

[keat]

I found something interesting, in that the search also picks up anything inside a hyperlinked document.

eg RCT1-B,

If I search just RCT, (partial product_code) the search will find it, because the product RCT1-B has a hyperlink to a pdf file which contains the phrase 'RCT'

 

[keat]

Would this work, and I guess it would have to go out to a coder as it's beyond my capabilities.

A drop down box in the search or advanced search, where a customer could choose whether he wanted to search by product_code, product_description or product_name.

If a customer then chose to search by product_code, it could be as fuzzy as it needs to be.

[bsmither]

A backend search is using the query format LIKE "%term%".

The Catalogue->searchCatalogue() starts with the FULLTEXT search (which discards any term having fewer characters than the minimum - 4, I think). MySQL's fulltext search is difficult to understand why it returns what it does.

Then, if nothing comes back, CubeCart switches to a simple regex search on whole words - partial words will not be found. That query format is RLIKE "[[:<:]]word[[:<:]]".

Then, if nothing comes back, CubeCart switches to a simple wildcard search - partial words will be found. That query format is LIKE "%characters%".

There are other conversations on these forums that discuss how to get CubeCart to skip the FULLTEXT and go straight to RLIKE.

All searches look at product_code, name, and description. Using plugins or code snippets, one can add other database columns and tables to look at. (I've done that.)

To a database engine, a link in the description is no different that regular text. So, 'RCT' in a link would be found, but not because it is being a link.

"the search also picks up anything inside a hyperlinked document"

I would dispute that. Perhaps a character sequence in the link, but not in the document the link points to.

[havenswift-hosting]

16 minutes ago, bsmither said:

The Catalogue->searchCatalogue() starts with the FULLTEXT search (which discards any term having fewer characters than the minimum - 4, I think). MySQL's fulltext search is difficult to understand why it returns what it does.

This is a function of MySQL where the full text search minimum character length defaults to 4.  This can be changed at a server level by changing ft_min_word_len and innodb_ft_min_token_size variables (see https://dev.mysql.com/doc/refman/5.7/en/fulltext-fine-tuning.html) and we will often change this to 3 on our servers (and so your search would work)

[Dirty Butter]

3 hours ago, bsmither said:

There are other conversations on these forums that discuss how to get CubeCart to skip the FULLTEXT and go straight to RLIKE.

All searches look at product_code, name, and description. Using plugins or code snippets, one can add other database columns and tables to look at. (I've done that.)

Bsmither coded our stores to use RLIKE Search on meta_keywords. Our plush store will now find results for a search for TY.

[keat]

14 hours ago, havenswift-hosting said:

This is a function of MySQL where the full text search minimum character length defaults to 4.  This can be changed at a server level by changing ft_min_word_len and innodb_ft_min_token_size variables (see https://dev.mysql.com/doc/refman/5.7/en/fulltext-fine-tuning.html) and we will often change this to 3 on our servers (and so your search would work)

I was hoping that this was a glimmer of hope, however, I created a test product 'test123456'

Searching for test1 through to test12345 revealed nothing. Only until i searched test123456 did my product appear.

 

My biggest issue is that the boss believes that a customer will want to search not only on product_name and description, but also by part numbers or partial part numbers, as was seen earlier in the week with SSHC.

Rather than navigating the many categories and then sub categories to find the stainless steel hose clip range, the customer might just want to find them using the term 'SSHC'

Of course logic would suggest that it might be easier to search 'Stainless Steel Hose Clips', however, we don't all think the same.

 

In regards to the hyperlink query, i failed to notice that the hyperlink was actually called 'rct.pdf'

[havenswift-hosting]

The ft_min_word_len restrictions (which 99% of users will not be able to change) are the reason the search was re-written the way it is which unfortunately still doesn't work very well

[keat]

If this isnt fixed in a later version, or any plans to do so, then I may end up looking for a mod of some sort.

[havenswift-hosting]

There was a good suggestive search mod originally V5 that helped a lot but the developer is no longer around (for a couple of years) and there are changes needed to the code to get it to work although quite a few sites do use this with V6 once modified.  Search is always a tricky topic in any product and what CubeCart really needs is a comprehensive search plugin that is configurable for multiple situations but it is a lot of work and could have ongoing cost implications depending on how complex it gets and what search engine is used.

[keat]

Which i guess leads back to my previous comment.

Would this work

A drop down box in the search or advanced search, where a customer could choose whether he wanted to search by product_code, product_description or product_name.

If a customer then chose to search by product_code, it could be as fuzzy as it needs to be.