Tweaking prestashop search - searching by products codes (reference)

Prestashop search engine is based on individual words stored in the database. It will return no results for incomplete (partial) words.

This problem was recently encountered on a website selling quad spare parts. Every spare part has a reference number in the form xxxxyyyzzzz where xxxx is specific for group , yyy for subgroup and zzzz for the product itself. Searching for "xxxx polaris" should return all spareparts belonging to the the group "xxxx" and matching the word polaris. But, as mentioned above, "xxxx" is only an incomplete word and will be normally ignored by the search engine. A simple solution is adding several lines of code into the indexation method of the Search class and rebuild the index:

if($key == 'reference') {
for($i = 5; $i < 10; $i++) {
if(strlen($value) > $i) {
$words[]=substr($value,0,$i);
}
}
}

 

 

Bellow is the complete code for Prestashop 1.5.6 (do not use for 1.6 without adaptation):

execute('TRUNCATE '._DB_PREFIX_.'search_index');
$db->execute('TRUNCATE '._DB_PREFIX_.'search_word');
ObjectModel::updateMultishopTable('Product', array('indexed' => 0));
}
else
{
// Do it even if you already know the product id in order to be sure that it exists and it needs to be indexed
$products = $db->executeS('
SELECT p.id_product
FROM '._DB_PREFIX_.'product p
'.Shop::addSqlAssociation('product', 'p').'
WHERE product_shop.visibility IN ("both", "search")
AND '.($id_product ? 'p.id_product = '.(int)$id_product : 'product_shop.indexed = 0')
);

$ids = array();
if ($products)
foreach ($products as $product)
$ids[] = (int)$product['id_product'];
if (count($ids))
{
$db->execute('DELETE FROM '._DB_PREFIX_.'search_index WHERE id_product IN ('.implode(',', $ids).')');
ObjectModel::updateMultishopTable('Product', array('indexed' => 0), 'a.id_product IN ('.implode(',', $ids).')');
}
}

// Every fields are weighted according to the configuration in the backend
$weight_array = array(
'pname' => Configuration::get('PS_SEARCH_WEIGHT_PNAME'),
'reference' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
'ean13' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
'upc' => Configuration::get('PS_SEARCH_WEIGHT_REF'),
'description_short' => Configuration::get('PS_SEARCH_WEIGHT_SHORTDESC'),
'description' => Configuration::get('PS_SEARCH_WEIGHT_DESC'),
'cname' => Configuration::get('PS_SEARCH_WEIGHT_CNAME'),
'mname' => Configuration::get('PS_SEARCH_WEIGHT_MNAME'),
'tags' => Configuration::get('PS_SEARCH_WEIGHT_TAG'),
'attributes' => Configuration::get('PS_SEARCH_WEIGHT_ATTRIBUTE'),
'features' => Configuration::get('PS_SEARCH_WEIGHT_FEATURE')
);

// Those are kind of global variables required to save the processed data in the database every X occurrences, in order to avoid overloading MySQL
$count_words = 0;
$query_array3 = array();

// Every indexed words are cached into a PHP array
$word_ids = $db->executeS('
SELECT id_word, word, id_lang, id_shop
FROM '._DB_PREFIX_.'search_word', false);
$word_ids_by_word = array();
while ($word_id = $db->nextRow($word_ids))
{
if (!isset($word_ids_by_word[$word_id['id_shop']][$word_id['id_lang']]))
$word_ids_by_word[$word_id['id_shop']][$word_id['id_lang']] = array();
$word_ids_by_word[$word_id['id_shop']][$word_id['id_lang']]['_'.$word_id['word']] = (int)$word_id['id_word'];
}

// Retrieve the number of languages
$total_languages = count(Language::getLanguages(false));

// Products are processed 50 by 50 in order to avoid overloading MySQL
while (($products = Search::getProductsToIndex($total_languages, $id_product, 50)) && (count($products) > 0))
{
$products_array = array();
// Now each non-indexed product is processed one by one, langage by langage
foreach ($products as $product)
{
$product['tags'] = Search::getTags($db, (int)$product['id_product'], (int)$product['id_lang']);
$product['attributes'] = Search::getAttributes($db, (int)$product['id_product'], (int)$product['id_lang']);
$product['features'] = Search::getFeatures($db, (int)$product['id_product'], (int)$product['id_lang']);

// Data must be cleaned of html, bad characters, spaces and anything, then if the resulting words are long enough, they're added to the array
$product_array = array();
foreach ($product as $key => $value)
if (strncmp($key, 'id_', 3) && isset($weight_array[$key]))
{ // tady
$words = explode(' ', Search::sanitize($value, (int)$product['id_lang'], true, $product['iso_code']));
if($key == 'reference') { for($i = 5; $i < 14; $i++) { if(strlen($value) > $i) { $words[]=substr($value,0,$i); } } }

foreach ($words as $word)
if (!empty($word))
{
$word = Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH);
// Remove accents
$word = Tools::replaceAccentedChars($word);

if (!isset($product_array[$word]))
$product_array[$word] = 0;
$product_array[$word] += $weight_array[$key];
}
}

// If we find words that need to be indexed, they're added to the word table in the database
if (count($product_array))
{
$query_array = $query_array2 = array();
foreach ($product_array as $word => $weight)
if ($weight && !isset($word_ids_by_word['_'.$word]))
{
$query_array[$word] = '('.(int)$product['id_lang'].', '.(int)$product['id_shop'].', \''.pSQL($word).'\')';
$query_array2[] = '\''.pSQL($word).'\'';
$word_ids_by_word[$product['id_shop']][$product['id_lang']]['_'.$word] = 0;
}

if ($query_array2)
{
$existing_words = $db->executeS('
SELECT DISTINCT word FROM '._DB_PREFIX_.'search_word
WHERE word IN ('.implode(',', $query_array2).')
AND id_lang = '.(int)$product['id_lang'].'
AND id_shop = '.(int)$product['id_shop']);

foreach ($existing_words as $data)
unset($query_array[Tools::replaceAccentedChars($data['word'])]);
}

if (count($query_array))
{
// The words are inserted...
$db->execute('
INSERT IGNORE INTO '._DB_PREFIX_.'search_word (id_lang, id_shop, word)
VALUES '.implode(',', $query_array));
}
if (count($query_array2))
{
// ...then their IDs are retrieved and added to the cache
$added_words = $db->executeS('
SELECT sw.id_word, sw.word
FROM '._DB_PREFIX_.'search_word sw
WHERE sw.word IN ('.implode(',', $query_array2).')
AND sw.id_lang = '.(int)$product['id_lang'].'
AND sw.id_shop = '.(int)$product['id_shop'].'
LIMIT '.count($query_array2));
// replace accents from the retrieved words so that words without accents or with differents accents can still be linked
foreach ($added_words as $word_id)
$word_ids_by_word[$product['id_shop']][$product['id_lang']]['_'.Tools::replaceAccentedChars($word_id['word'])] = (int)$word_id['id_word'];
}
}

foreach ($product_array as $word => $weight)
{
if (!$weight)
continue;
if (!isset($word_ids_by_word[$product['id_shop']][$product['id_lang']]['_'.$word]))
continue;
if (!$word_ids_by_word[$product['id_shop']][$product['id_lang']]['_'.$word])
continue;
$query_array3[] = '('.(int)$product['id_product'].','.
(int)$word_ids_by_word[$product['id_shop']][$product['id_lang']]['_'.$word].','.(int)$weight.')';
// Force save every 200 words in order to avoid overloading MySQL
if (++$count_words % 200 == 0)
Search::saveIndex($query_array3);
}

if (!in_array($product['id_product'], $products_array))
$products_array[] = (int)$product['id_product'];
}
Search::setProductsAsIndexed($products_array);

// One last save is done at the end in order to save what's left
Search::saveIndex($query_array3);
}
return true;
}



}

 

webdnes.cz
®2016