[SOLVED] Substring search support

[sinoland]

Hello,

Product names in some languages, e.g. Chinese language, are often formed by two or more words (without space). For proper display of search results, we need substring search support.

After studying some threads, we found a possible solution as described in the next post. Thank you.

Regards,
Mr. Tsang

[sinoland]

Found a solution but not sure whether it is complete or not.

Replace classes/Search.php lines 155 to 158

                    AND sw.word LIKE '.($word[0] == '-' ? ' \''.pSQL(Tools::substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)).'%\'' : '\''.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\'').'
               ) ';
               if ($word[0] != '-')
                   $scoreArray[] = 'sw.word LIKE \''.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\'';

with the following

                    AND sw.word LIKE '.($word[0] == '-' ? ' \''.'%'.pSQL(Tools::substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)).'%\'' : '\''.'%'.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\'').'
               ) ';
               if ($word[0] != '-')
                   $scoreArray[] = 'sw.word LIKE \''.'%'.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\'';

Basically, the replacement adds .'%' right before .pSQL

Now, if you search for 'book', MacBook would be listed.

[ciceron5978]

Sorry but it doesn't work !!
It is working for you ?

[Jubri]

Me too, doesn't work

This Freeze MySQL:

SELECT DISTINCT cp.`id_product`
FROM `ps_category_group` cg
INNER JOIN `ps_category_product` cp ON cp.`id_category` = cg.`id_category`
INNER JOIN `ps_category` c ON cp.`id_category` = c.`id_category`
INNER JOIN `ps_product` p ON cp.`id_product` = p.`id_product`
WHERE c.`active` = 1 AND p.`active` = 1
AND cg.`id_group` = 1
AND  p.id_product  IN (
           SELECT id_product
           FROM ps_search_word sw
           LEFT JOIN ps_search_index si ON sw.id_word = si.id_word
           WHERE sw.id_lang = 3
           AND sw.word LIKE '% F4580%'
       ) 

Space after % not exists, i can't write it without space at this forum



This Works OK:

SELECT DISTINCT cp.`id_product`
FROM `ps_category_group` cg
INNER JOIN `ps_category_product` cp ON cp.`id_category` = cg.`id_category`
INNER JOIN `ps_category` c ON cp.`id_category` = c.`id_category`
INNER JOIN `ps_product` p ON cp.`id_product` = p.`id_product`
WHERE c.`active` = 1 AND p.`active` = 1
AND cg.`id_group` = 1
AND  p.id_product  IN (
           SELECT id_product
           FROM ps_search_word sw
           LEFT JOIN ps_search_index si ON sw.id_word = si.id_word
           WHERE sw.id_lang = 3
           AND sw.word LIKE 'F4580%'
       ) 

It's seems be a loop a database level...

'SubQuery', works fine individually at two examples.

Any suggestion??


Thanks

[Jubri]

MySQL version: 5.1.50-community

It's seems a MySQL bug...

http://bugs.mysql.com/bug.php?id=35042

...
yes, the subquery is re-evaluated every time because it is made correlated by
IN->EXISTS transformation (for details see
http://en.oreilly.com/mysql2008/public/asset/attachment/2128, slide #5 and around).
...

[ciceron5978]

so finally do we have a solution ?

[Vaidas]

I have the same bug. When there is empty space between word, search didn't work corectly. Have any ideas?

[faDdy]

I have the same problem ... anyone ?