Searching and indexing dashed and dotted references

[pranab13]

Hello,

 

I tried to look for this problem somewhere else but couldn't find anything.

 

We have a shop with 11000 products and half of the references are with a dash, a slash or a dot. It exists like that since 15 years so it wasn't thought for the internet. Example : "154-1578" "23.654" "lplb-x7v" "K502.8/10"...

 

1/ Prestashop doesn't take into account the dashes or dots, I noticed it's because of the sanitize and find functions on the Search.php class. Also it explodes the words so "154-1578" is becoming "154" and "1578", hence the results are incorrects and the product targeted isn't the first on the results or doesn't appear. People could think that the product doesn't exist.

 

I got around that overriding the Search class like that : 

 

<?php


/* 
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */


define('PREG_CLASS_PUNCTUATION',
    '\x{21}-\x{23}\x{25}-\x{2a}\x{2c}\x{2e}\x{2f}\x{3a}\x{3b}\x{3f}\x{40}\x{5b}-\x{5d}'.
    '\x{5f}\x{7b}\x{7d}\x{a1}\x{ab}\x{b7}\x{bb}\x{bf}\x{37e}\x{387}\x{55a}-\x{55f}'.
    '\x{589}\x{58a}\x{5be}\x{5c0}\x{5c3}\x{5f3}\x{5f4}\x{60c}\x{60d}\x{61b}\x{61f}'.
    '\x{66a}-\x{66d}\x{6d4}\x{700}-\x{70d}\x{964}\x{965}\x{970}\x{df4}\x{e4f}'.
    '\x{e5a}\x{e5b}\x{f04}-\x{f12}\x{f3a}-\x{f3d}\x{f85}\x{104a}-\x{104f}\x{10fb}'.
    '\x{1361}-\x{1368}\x{166d}\x{166e}\x{169b}\x{169c}\x{16eb}-\x{16ed}\x{1735}'.
    '\x{1736}\x{17d4}-\x{17d6}\x{17d8}-\x{17da}\x{1800}-\x{180a}\x{1944}\x{1945}'.
    '\x{2010}-\x{2027}\x{2030}-\x{2043}\x{2045}-\x{2051}\x{2053}\x{2054}\x{2057}'.
    '\x{207d}\x{207e}\x{208d}\x{208e}\x{2329}\x{232a}\x{23b4}-\x{23b6}\x{2768}-'.
    '\x{2775}\x{27e6}-\x{27eb}\x{2983}-\x{2998}\x{29d8}-\x{29db}\x{29fc}\x{29fd}'.
    '\x{3001}-\x{3003}\x{3008}-\x{3011}\x{3014}-\x{301f}\x{3030}\x{303d}\x{30a0}'.
    '\x{30fb}\x{fd3e}\x{fd3f}\x{fe30}-\x{fe52}\x{fe54}-\x{fe61}\x{fe63}\x{fe68}'.
    '\x{fe6a}\x{fe6b}\x{ff01}-\x{ff03}\x{ff05}-\x{ff0a}\x{ff0c}-\x{ff0f}\x{ff1a}'.
    '\x{ff1b}\x{ff1f}\x{ff20}\x{ff3b}-\x{ff3d}\x{ff3f}\x{ff5b}\x{ff5d}\x{ff5f}-'.
    '\x{ff65}');


class Search extends SearchCore
{    
    public static function sanitize($string, $id_lang, $indexation = false, $iso_code = false)
    {
        $string = trim($string);
        if (empty($string)) {
            return '';
        }


        $string = Tools::strtolower(strip_tags($string));
        $string = html_entity_decode($string, ENT_NOQUOTES, 'utf-8');


        $string = preg_replace('/(['.PREG_CLASS_NUMBERS.']+)['.PREG_CLASS_PUNCTUATION.']+(?=['.PREG_CLASS_NUMBERS.'])/u', '\1', $string);
        $string = preg_replace('/['.PREG_CLASS_SEARCH_EXCLUDE.']+/u', ' ', $string);


        if ($indexation) {
            //$string = preg_replace('/[._-]+/', ' ', $string);
        } else {
            $words = explode(' ', $string);
            $processed_words = array();
            // search for aliases for each word of the query
            foreach ($words as $word) {
                $alias = new Alias(null, $word);
                if (Validate::isLoadedObject($alias)) {
                    $processed_words[] = $alias->search;
                } else {
                    $processed_words[] = $word;
                }
            }
            $string = implode(' ', $processed_words);
            //$string = preg_replace('/[._]+/', '', $string);
            //$string = ltrim(preg_replace('/([^ ])-/', '$1 ', ' '.$string));
            //$string = preg_replace('/[._]+/', '', $string);
            //$string = preg_replace('/[^\s]-+/', '', $string);
        }


        $blacklist = Tools::strtolower(Configuration::get('PS_SEARCH_BLACKLIST', $id_lang));
        if (!empty($blacklist)) {
            $string = preg_replace('/(?<=\s)('.$blacklist.')(?=\s)/Su', '', $string);
            $string = preg_replace('/^('.$blacklist.')(?=\s)/Su', '', $string);
            $string = preg_replace('/(?<=\s)('.$blacklist.')$/Su', '', $string);
            $string = preg_replace('/^('.$blacklist.')$/Su', '', $string);
        }


        // If the language is constituted with symbol and there is no "words", then split every chars
        if (in_array($iso_code, array('zh', 'tw', 'ja')) && function_exists('mb_strlen')) {
            // Cut symbols from letters
            $symbols = '';
            $letters = '';
            foreach (explode(' ', $string) as $mb_word) {
                if (strlen(Tools::replaceAccentedChars($mb_word)) == mb_strlen(Tools::replaceAccentedChars($mb_word))) {
                    $letters .= $mb_word.' ';
                } else {
                    $symbols .= $mb_word.' ';
                }
            }


            if (preg_match_all('/./u', $symbols, $matches)) {
                $symbols = implode(' ', $matches[0]);
            }


            $string = $letters.$symbols;
        } elseif ($indexation) {
            $minWordLen = (int)Configuration::get('PS_SEARCH_MINWORDLEN');
            if ($minWordLen > 1) {
                $minWordLen -= 1;
                $string = preg_replace('/(?<=\s)[^\s]{1,'.$minWordLen.'}(?=\s)/Su', ' ', $string);
                $string = preg_replace('/^[^\s]{1,'.$minWordLen.'}(?=\s)/Su', '', $string);
                $string = preg_replace('/(?<=\s)[^\s]{1,'.$minWordLen.'}$/Su', '', $string);
                $string = preg_replace('/^[^\s]{1,'.$minWordLen.'}$/Su', '', $string);
            }
        }


        $string = Tools::replaceAccentedChars(trim(preg_replace('/\s+/', ' ', $string)));


        return $string;
    }
    
    public static function find($id_lang, $expr, $page_number = 1, $page_size = 1, $order_by = 'position',
        $order_way = 'desc', $ajax = false, $use_cookie = true, Context $context = null)
    {
        if (!$context) {
            $context = Context::getContext();
        }
        $db = Db::getInstance(_PS_USE_SQL_SLAVE_);


        // TODO : smart page management
        if ($page_number < 1) {
            $page_number = 1;
        }
        if ($page_size < 1) {
            $page_size = 1;
        }


        if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way)) {
            return false;
        }


        $intersect_array = array();
        $score_array = array();
        $words = explode(' ', Search::sanitize($expr, $id_lang, false, $context->language->iso_code));


        foreach ($words as $key => $word) {
            if (!empty($word) && strlen($word) >= (int)Configuration::get('PS_SEARCH_MINWORDLEN')) {
                $word = str_replace(array('%', '_'), array('\\%', '\\_'), $word);
                $start_search = Configuration::get('PS_SEARCH_START') ? '%': '';
                $end_search = Configuration::get('PS_SEARCH_END') ? '': '%';


                $intersect_array[] = 'SELECT si.id_product
FROM '._DB_PREFIX_.'search_word sw
LEFT JOIN '._DB_PREFIX_.'search_index si ON sw.id_word = si.id_word
WHERE sw.id_lang = '.(int)$id_lang.'
AND sw.id_shop = '.$context->shop->id.'
AND sw.word LIKE
'.($word[0] == '-'
                        ? ' \'%'.$start_search.pSQL(Tools::substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)).$end_search.'%\''
                        : ' \'%'.$start_search.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).$end_search.'%\''
                    );


                if ($word[0] != '-') {
                    $score_array[] = 'sw.word LIKE \'%'.$start_search.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).$end_search.'%\'';
                }
            } else {
                unset($words[$key]);
            }
        }


        if (!count($words)) {
            return ($ajax ? array() : array('total' => 0, 'result' => array()));
        }


        $score = '';
        if (is_array($score_array) && !empty($score_array)) {
            $score = ',(
SELECT SUM(weight)
FROM '._DB_PREFIX_.'search_word sw
LEFT JOIN '._DB_PREFIX_.'search_index si ON sw.id_word = si.id_word
WHERE sw.id_lang = '.(int)$id_lang.'
AND sw.id_shop = '.$context->shop->id.'
AND si.id_product = p.id_product
AND ('.implode(' OR ', $score_array).')
) position';
        }


        $sql_groups = '';
        if (Group::isFeatureActive()) {
            $groups = FrontController::getCurrentCustomerGroups();
            $sql_groups = 'AND cg.`id_group` '.(count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1');
        }


        $results = $db->executeS('
SELECT cp.`id_product`
FROM `'._DB_PREFIX_.'category_product` cp
'.(Group::isFeatureActive() ? 'INNER JOIN `'._DB_PREFIX_.'category_group` cg ON cp.`id_category` = cg.`id_category`' : '').'
INNER JOIN `'._DB_PREFIX_.'category` c ON cp.`id_category` = c.`id_category`
INNER JOIN `'._DB_PREFIX_.'product` p ON cp.`id_product` = p.`id_product`
'.Shop::addSqlAssociation('product', 'p', false).'
WHERE c.`active` = 1
AND product_shop.`active` = 1
AND product_shop.`visibility` IN ("both", "search")
AND product_shop.indexed = 1
'.$sql_groups, true, false);


        $eligible_products = array();
        foreach ($results as $row) {
            $eligible_products[] = $row['id_product'];
        }
        foreach ($intersect_array as $query) {
            $eligible_products2 = array();
            foreach ($db->executeS($query, true, false) as $row) {
                $eligible_products2[] = $row['id_product'];
            }


            $eligible_products = array_intersect($eligible_products, $eligible_products2);
            if (!count($eligible_products)) {
                return ($ajax ? array() : array('total' => 0, 'result' => array()));
            }
        }


        $eligible_products = array_unique($eligible_products);


        $product_pool = '';
        foreach ($eligible_products as $id_product) {
            if ($id_product) {
                $product_pool .= (int)$id_product.',';
            }
        }
        if (empty($product_pool)) {
            return ($ajax ? array() : array('total' => 0, 'result' => array()));
        }
        $product_pool = ((strpos($product_pool, ',') === false) ? (' = '.(int)$product_pool.' ') : (' IN ('.rtrim($product_pool, ',').') '));


        if ($ajax) {
            $sql = 'SELECT DISTINCT p.id_product, pl.name pname, cl.name cname,
cl.link_rewrite crewrite, pl.link_rewrite prewrite '.$score.'
FROM '._DB_PREFIX_.'product p
INNER JOIN `'._DB_PREFIX_.'product_lang` pl ON (
p.`id_product` = pl.`id_product`
AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').'
)
'.Shop::addSqlAssociation('product', 'p').'
INNER JOIN `'._DB_PREFIX_.'category_lang` cl ON (
product_shop.`id_category_default` = cl.`id_category`
AND cl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('cl').'
)
WHERE p.`id_product` '.$product_pool.'
ORDER BY position DESC LIMIT 10';
            return $db->executeS($sql, true, false);
        }


        if (strpos($order_by, '.') > 0) {
            $order_by = explode('.', $order_by);
            $order_by = pSQL($order_by[0]).'.`'.pSQL($order_by[1]).'`';
        }
        $alias = '';
        if ($order_by == 'price') {
            $alias = 'product_shop.';
        } elseif (in_array($order_by, array('date_upd', 'date_add'))) {
            $alias = 'p.';
        }
        $sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity,
pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`,
image_shop.`id_image` id_image, il.`legend`, m.`name` manufacturer_name '.$score.',
DATEDIFF(
p.`date_add`,
DATE_SUB(
"'.date('Y-m-d').' 00:00:00",
INTERVAL '.(Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY
)
) > 0 new'.(Combination::isFeatureActive() ? ', product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity, IFNULL(product_attribute_shop.`id_product_attribute`,0) id_product_attribute' : '').'
FROM '._DB_PREFIX_.'product p
'.Shop::addSqlAssociation('product', 'p').'
INNER JOIN `'._DB_PREFIX_.'product_lang` pl ON (
p.`id_product` = pl.`id_product`
AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').'
)
'.(Combination::isFeatureActive() ? 'LEFT JOIN `'._DB_PREFIX_.'product_attribute_shop` product_attribute_shop
ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop='.(int)$context->shop->id.')':'').'
'.Product::sqlStock('p', 0).'
LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
LEFT JOIN `'._DB_PREFIX_.'image_shop` image_shop
ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop='.(int)$context->shop->id.')
LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$id_lang.')
WHERE p.`id_product` '.$product_pool.'
GROUP BY product_shop.id_product
'.($order_by ? 'ORDER BY  '.$alias.$order_by : '').($order_way ? ' '.$order_way : '').'
LIMIT '.(int)(($page_number - 1) * $page_size).','.(int)$page_size;
        $result = $db->executeS($sql, true, false);


        $sql = 'SELECT COUNT(*)
FROM '._DB_PREFIX_.'product p
'.Shop::addSqlAssociation('product', 'p').'
INNER JOIN `'._DB_PREFIX_.'product_lang` pl ON (
p.`id_product` = pl.`id_product`
AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').'
)
LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE p.`id_product` '.$product_pool;
        $total = $db->getValue($sql, false);


        if (!$result) {
            $result_properties = false;
        } else {
            $result_properties = Product::getProductsProperties((int)$id_lang, $result);
        }


        return array('total' => $total,'result' => $result_properties);
    }
}

There are two things I've changed. I've allowed \x{2d} in the preg class, which is the dash, to be taken into account and I've commented 4 lines where the dash and point are ignored.

• Does someone know how to redo these commented rules without ignoring the dash and the dot ?
• Is there another way for it to read the dashes, dots and slashes and not explode the words with those signs ?
• The override I've created, is it safe ?

The references with letters seem to work better than the ones with only numbers. Because when searching for "152-2356" it is going to find products with "15.2" "1.52" "23.56" in the description text or features value. So it appears before the reference searched... With letters it's more precised.

 

 

2/ Another thing explaining the first problem :

Prestashop doesn't index words with dashes, dots... The references are not indexed so even if I have my override, if it's not in the index, it won't be found. I have to enter them myself in the PS_search_word table and link them to the product in the PS_search_index for it to be found when searched with my new override.

 

• Does someone know how to get around that so the references with dashes, dots and slashes are indexed ?

 

I can enter them in the table directly but everytime there's a reindexation, the table is truncated and refilled so everything is lost...

 

The weights are :

Product 7

Reference 10

Short desc. 4

Long desc. 9

Category 7

Manufacturer5

Keywords 6

Attributes 1

Features 8

 

Can someone help me ?

Did you have this problem

Edited June 13, 2017 by pranab13 (see edit history)

[pranab13]

Anyone ?

 

And that's confirmed, it only doesn't index references with only numbers and dash. When there are letters, it is indexed.

[pranab13]

I'm still stuck with that problem and I have to import a list of dashed and dotted references everytime I rebuild the index.

 

Is there a way to add on the indexation function something like the following ? And making it taken into account when the Search::indexation function is called like in the cron task or the links to rebuilt in the search admin panel.

return Db::getInstance()->execute('INSERT IGNORE INTO `'._DB_PREFIX_.'search_word` (`id_word`, `id_shop`, `id_lang`, `word`) '
            . 'VALUES '
            . '(1, 1, 1, \'01-100\'),
            (2, 1, 1, \'01-101\')');

return Db::getInstance()->execute('INSERT IGNORE INTO `'._DB_PREFIX_.'search_index` (`id_product`, `id_word`, `weight`) '
            . 'VALUES '
            . '(1, 1, 1, \'01-100\'),
            (1, 1, 200)');

That way I won't have to go to PHPMyAdmin everytime. And I can enable the cron task for indexation.

[MiguelGG]

Hello,

Sorry, this is a Google Translate translation.

I have the same problem with searches by reference. All my references include letters, numbers, hyphens (-) and underscores (_), for example: BF-17_13.

Have you found any solution? Thank you so much.

Miguel G.

[pranab13]

Hello,

 

The only thing I found, which is not a final solution, is to put the reference as a tag (the prestashop keywords).

You put the override of the Search.php class and then for every product you put a tag with their reference. You can do an import to do it quickly.

It works depending of your references, if there's at least a letter in it, it will works better than if there's only numbers.

That way when a customer will search BF-17_13, the product will show inthe list because the tag BF-17_13 is attached. Don't forget to put the weight of the tags as 10.

Quick note : on my override I allowed only the dashes and the dots, not the underscores.

 

Intento de traducir:

La unica "solucion" que encontré es de poner la referencia como palabra clave del producto Prestashop.

Pon el Search.php override que di arriba y despues por cada producto pon la referencia como palabra clave. Puedes importarlos. Asi cuando el cliente busca la referencia, Prestahop la encontrara porque la palabra clave es conectada.

Funciona pero depende de tus referencias. He notado que con las referencias con al menos una letra funciona mejor que con referencias con solo numeros.

No te olvides de poner el peso de las palabras claves a 10.

En my override, solo permiti los guiones y los puntos, no el underscore.

 

Anaïs

 

[MiguelGG]

thank you very much for everything

[ajensen27]

I know it's been a while but I was looking for something similar (searches just part of reference, i.e. search for 123 would still return product with reference abc123). This module seems to do this as well as allow for underscores at the least. I haven't purchased it yet but will be soon.

 

https://www.jose-aguilar.com/modulos-prestashop/en/24-block-search-suggestions.html