public function __construct() { global $cookie; $this->name = 'blocklayered'; $this->tab = 'front_office_features'; $this->version = 1.3; $this->author = 'PrestaShop'; $this->need_instance = 0; $this->resultSet = null; parent::__construct(); $this->displayName = $this->l('Layered navigation block'); $this->description = $this->l('Displays a block with layered navigation filters.'); $this->search_query = Search::sanitize(Tools::getValue('search_query'), (int) $cookie->id_lang); }
public static function find($id_lang, $expr, $pageNumber = 1, $pageSize = 1, $orderBy = 'position', $orderWay = 'desc', $ajax = false, $useCookie = true, Context $context = null) { global $cookie; if (!Module::isInstalled('agilemultipleseller') and !Module::isInstalled('agilesellerlistoptions')) { return parent::find($id_lang, $expr, $pageNumber, $pageSize, $orderBy, $orderWay, $ajax, $useCookie); } $agile_sql_parts = AgileSellerManager::getAdditionalSqlForProducts("p"); $db = Db::getInstance(_PS_USE_SQL_SLAVE_); if ($useCookie) { $id_customer = (int) $cookie->id_customer; } else { $id_customer = 0; } if ($pageNumber < 1) { $pageNumber = 1; } if ($pageSize < 1) { $pageSize = 1; } if (!Validate::isOrderBy($orderBy) or !Validate::isOrderWay($orderWay)) { return false; } $intersectArray = array(); $scoreArray = array(); $words = explode(' ', Search::sanitize($expr, (int) $id_lang)); foreach ($words as $key => $word) { if (!empty($word) and strlen($word) >= (int) Configuration::get('PS_SEARCH_MINWORDLEN')) { $word = str_replace('%', '\\%', $word); $word = str_replace('_', '\\_', $word); $intersectArray[] = 'SELECT 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.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)) . '%\''; } } else { unset($words[$key]); } } if (!sizeof($words)) { return $ajax ? array() : array('total' => 0, 'result' => array()); } $score = ''; if (sizeof($scoreArray)) { $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 si.id_product = p.id_product AND (' . implode(' OR ', $scoreArray) . ') ) position'; } $result = $db->ExecuteS(' SELECT cp.`id_product` FROM `' . _DB_PREFIX_ . 'category_group` cg INNER JOIN `' . _DB_PREFIX_ . 'category_product` cp 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` WHERE c.`active` = 1 AND p.`active` = 1 AND indexed = 1 AND cg.`id_group` ' . (!$id_customer ? '= 1' : 'IN ( SELECT id_group FROM ' . _DB_PREFIX_ . 'customer_group WHERE id_customer = ' . (int) $id_customer . ' )'), false); $eligibleProducts = array(); while ($row = $db->nextRow($result)) { $eligibleProducts[] = $row['id_product']; } foreach ($intersectArray as $query) { $result = $db->ExecuteS($query, false); $eligibleProducts2 = array(); while ($row = $db->nextRow($result)) { $eligibleProducts2[] = $row['id_product']; } $eligibleProducts = array_intersect($eligibleProducts, $eligibleProducts2); if (!count($eligibleProducts)) { return $ajax ? array() : array('total' => 0, 'result' => array()); } } array_unique($eligibleProducts); $productPool = ''; foreach ($eligibleProducts as $id_product) { if ($id_product) { $productPool .= (int) $id_product . ','; } } if (empty($productPool)) { return $ajax ? array() : array('total' => 0, 'result' => array()); } $productPool = strpos($productPool, ',') === false ? ' = ' . (int) $productPool . ' ' : ' IN (' . rtrim($productPool, ',') . ') '; if ($ajax) { $sql = 'SELECT DISTINCT p.id_product, pl.name pname, cl.name cname, cl.link_rewrite crewrite, pl.link_rewrite prewrite ' . $score . ' ' . $agile_sql_parts['selects'] . ' 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') . ' ) ' . $agile_sql_parts['joins'] . ' WHERE p.`id_product` ' . $productPool . ' ' . $agile_sql_parts['wheres'] . ' ORDER BY position DESC LIMIT 10'; return $db->executeS($sql); } $from_conds = ' FROM ' . _DB_PREFIX_ . 'product p ' . $agile_sql_parts['joins'] . ' INNER JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . (int) $id_lang . ') LEFT JOIN `' . _DB_PREFIX_ . 'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group` AND tr.`id_country` = ' . (int) (_PS_VERSION_ > '1.5' ? Context::getContext()->country->id : Country::getDefaultCountryId()) . ' AND tr.`id_state` = 0) LEFT JOIN `' . _DB_PREFIX_ . 'tax` tax ON (tax.`id_tax` = tr.`id_tax`) LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer` LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1) LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . (int) $id_lang . ') WHERE p.`id_product` ' . $productPool . ' ' . $agile_sql_parts['wheres'] . ' '; $sort_limit = ($orderBy ? 'ORDER BY ' . $orderBy : '') . ($orderWay ? ' ' . $orderWay : '') . ' LIMIT ' . (int) (($pageNumber - 1) * $pageSize) . ',' . (int) $pageSize; $total = $db->getValue(' SELECT COUNT(*) ' . $from_conds); $queryResults = ' SELECT p.*, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`, tax.`rate`, i.`id_image`, il.`legend`, m.`name` manufacturer_name ' . $score . ', DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY)) > 0 new ' . $agile_sql_parts['selects'] . ' ' . $from_conds . ' ' . $sort_limit . ' '; $result = $db->ExecuteS($queryResults); if (!$result) { $resultProperties = false; } else { $resultProperties = Product::getProductsProperties((int) $id_lang, $result); } $resultProperties = AgileSellerManager::prepareSellerRattingInfo($resultProperties); return array('total' => $total, 'result' => $resultProperties); }
public static function indexation($full = false, $id_product = false) { $db = Db::getInstance(); if ($id_product) { $full = false; } if ($full) { $db->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])) { $words = explode(' ', Search::sanitize($value, (int) $product['id_lang'], true, $product['iso_code'])); 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; }
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_); 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('%', '\\%', $word); $word = str_replace('_', '\\_', $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(), 'painters' => array()); } $score = ''; if (count($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); $eligible_products = array(); foreach ($results as $row) { $eligible_products[] = $row['id_product']; } foreach ($intersect_array as $query) { $eligible_products2 = array(); foreach ($db->executeS($query) 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(), 'painters' => 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); } 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`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` manufacturer_name ' . $score . (Combination::isFeatureActive() ? ', MAX(product_attribute_shop.`id_product_attribute`) id_product_attribute' : '') . ', DATEDIFF( p.`date_add`, DATE_SUB( NOW(), INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . ' DAY ) ) > 0 new' . (Combination::isFeatureActive() ? ', MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity' : '') . ' 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` pa ON (p.`id_product` = pa.`id_product`) ' . Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1') . ' ' . Product::sqlStock('p', 'product_attribute_shop', false, $context->shop) : Product::sqlStock('p', 'product', false, Context::getContext()->shop)) . ' LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer` LEFT JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product`)' . Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1') . ' LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`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); $helper = new Helper(); $categoryTree = $helper->renderCategoryTree(); $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); if (!$result) { $result_properties = false; } else { $result_properties = Product::getProductsProperties((int) $id_lang, $result); } $sql = 'SELECT c.*, c_lang.* FROM ' . _DB_PREFIX_ . 'category c INNER JOIN ' . _DB_PREFIX_ . 'category_lang c_lang ON ( c.id_category = c_lang.id_category) WHERE c_lang.name LIKE ' . ($word[0] == '-' ? ' \'' . $end_search . pSQL(Tools::substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)) . $end_search . '\'' : ' \'' . $end_search . pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)) . $end_search . '\'') . 'AND c.id_parent = 14'; $painters = $db->executeS($sql); return array('total' => $total, 'result' => $result_properties, 'painters' => $painters); }
public static function indexation($full = false) { $db = Db::getInstance(); $dropIndex = false; if ($full) { $db->Execute('TRUNCATE ' . _DB_PREFIX_ . 'search_index'); $db->Execute('TRUNCATE ' . _DB_PREFIX_ . 'search_word'); $db->Execute('UPDATE ' . _DB_PREFIX_ . 'product SET indexed = 0'); $dropIndex = true; } else { $products = $db->ExecuteS('SELECT id_product FROM ' . _DB_PREFIX_ . 'product WHERE indexed = 0'); $ids = array(); if ($products) { foreach ($products as $product) { $ids[] = (int) $product['id_product']; } } if (sizeof($ids)) { //$db->Execute('DELETE FROM '._DB_PREFIX_.'search_index WHERE id_product IN ('.implode(',', $ids).')'); //reindex the products in solr index SolrSearch::updateProducts($ids); $products = $db->ExecuteS('UPDATE ' . _DB_PREFIX_ . 'product set indexed = 1 WHERE indexed = 0'); } if (count($ids)) { Db::getInstance()->Execute('UPDATE ' . _DB_PREFIX_ . 'product SET indexed = 1 WHERE id_product IN (' . implode(',', $ids) . ') LIMIT ' . (int) count($ids)); } if (count($products) > 2000) { $dropIndex = true; } } //do not do anything to the db index return; if ($dropIndex) { $dropIndex = false; $result = $db->ExecuteS('SHOW INDEX FROM `' . _DB_PREFIX_ . 'search_index`'); foreach ($result as $row) { if (strtolower($row['Key_name']) == 'primary') { $dropIndex = true; } } if ($dropIndex) { $db->Execute('ALTER TABLE ' . _DB_PREFIX_ . 'search_index DROP PRIMARY KEY'); } $dropIndex = true; } // Every fields are weighted according to the configuration in the backend $weightArray = 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')); // All the product not yet indexed are retrieved $products = $db->ExecuteS(' SELECT p.id_product, pl.id_lang, pl.name pname, p.reference, p.ean13, p.upc, pl.description_short, pl.description, cl.name cname, m.name mname FROM ' . _DB_PREFIX_ . 'product p LEFT JOIN ' . _DB_PREFIX_ . 'product_lang pl ON p.id_product = pl.id_product LEFT JOIN ' . _DB_PREFIX_ . 'category_lang cl ON (cl.id_category = p.id_category_default AND pl.id_lang = cl.id_lang) LEFT JOIN ' . _DB_PREFIX_ . 'manufacturer m ON m.id_manufacturer = p.id_manufacturer WHERE p.indexed = 0', false); // Those are kind of global variables required to save the processed data in the database every X occurences, in order to avoid overloading MySQL $countWords = 0; $countProducts = 0; $queryArray3 = array(); $productsArray = array(); // Every indexed words are cached into a PHP array $wordIdsByWord = array(); $wordIds = Db::getInstance()->ExecuteS(' SELECT sw.id_word, sw.word, id_lang FROM ' . _DB_PREFIX_ . 'search_word sw', false); $wordIdsByWord = array(); while ($wordId = $db->nextRow($wordIds)) { if (!isset($wordIdsByWord[$wordId['id_lang']])) { $wordIdsByWord[$wordId['id_lang']] = array(); } $wordIdsByWord[$wordId['id_lang']]['_' . $wordId['word']] = (int) $wordId['id_word']; } // Now each non-indexed product is processed one by one, langage by langage while ($product = $db->nextRow($products)) { $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 $pArray = array(); foreach ($product as $key => $value) { if (strncmp($key, 'id_', 3)) { $words = explode(' ', Search::sanitize($value, (int) $product['id_lang'], true)); foreach ($words as $word) { if (!empty($word)) { $word = Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH); if (!isset($pArray[$word])) { $pArray[$word] = 0; } $pArray[$word] += $weightArray[$key]; } } } } // If we find words that need to be indexed, they're added to the word table in the database if (sizeof($pArray)) { $list = ''; foreach ($pArray as $word => $weight) { $list .= '\'' . $word . '\','; } $list = rtrim($list, ','); $queryArray = array(); $queryArray2 = array(); foreach ($pArray as $word => $weight) { if ($weight and !isset($wordIdsByWord['_' . $word])) { $queryArray[] = '(' . (int) $product['id_lang'] . ',\'' . pSQL($word) . '\')'; $queryArray2[] = '\'' . pSQL($word) . '\''; $wordIdsByWord[$product['id_lang']]['_' . $word] = 0; } } if (count($queryArray)) { // The words are inserted... $db->Execute(' INSERT IGNORE INTO ' . _DB_PREFIX_ . 'search_word (id_lang, word) VALUES ' . implode(',', $queryArray)); // ...then their IDs are retrieved and added to the cache $addedWords = $db->ExecuteS(' SELECT sw.id_word, sw.word FROM ' . _DB_PREFIX_ . 'search_word sw WHERE sw.word IN (' . implode(',', $queryArray2) . ') AND sw.id_lang = ' . (int) $product['id_lang'] . ' LIMIT ' . count($queryArray2)); foreach ($addedWords as $wordId) { $wordIdsByWord[$product['id_lang']]['_' . $wordId['word']] = (int) $wordId['id_word']; } } } foreach ($pArray as $word => $weight) { if (!$weight) { continue; } if (!isset($wordIdsByWord[$product['id_lang']]['_' . $word])) { continue; } if (!$wordIdsByWord[$product['id_lang']]['_' . $word]) { continue; } $queryArray3[] = '(' . (int) $product['id_product'] . ',' . (int) $wordIdsByWord[$product['id_lang']]['_' . $word] . ',' . (int) $weight . ')'; // Force save every 200 words in order to avoid overloading MySQL if (++$countWords % 200 == 0) { Search::saveIndex($queryArray3); } } if (!in_array($product['id_product'], $productsArray)) { $productsArray[] = (int) $product['id_product']; } // Force save every 50 products in order to avoid overloading MySQL if (++$countProducts % 50 == 0) { Search::setProductsAsIndexed($productsArray); } } // One last save is done at the end in order to save what's left Search::saveIndex($queryArray3); Search::setProductsAsIndexed($productsArray); // If it has been deleted, the index is created again once the indexation is done if (!$dropIndex) { $dropIndex = true; $result = $db->ExecuteS('SHOW INDEX FROM `' . _DB_PREFIX_ . 'search_index`'); foreach ($result as $row) { if (strtolower($row['Key_name']) == 'primary') { $dropIndex = false; } } } if ($dropIndex) { $db->Execute('ALTER TABLE `' . _DB_PREFIX_ . 'search_index` ADD PRIMARY KEY (`id_word`, `id_product`)'); } Configuration::updateValue('PS_NEED_REBUILD_INDEX', 0); return true; }
public static function find($id_lang, $expr, $pageNumber = 1, $pageSize = 1, $orderBy = 'position', $orderWay = 'desc', $ajax = false) { global $cookie; // TODO : smart page management if ($pageNumber < 1) { $pageNumber = 1; } if ($pageSize < 1) { $pageSize = 1; } if (!Validate::isOrderBy($orderBy) or !Validate::isOrderWay($orderWay)) { die(Tools::displayError()); } $whereArray = array(); $scoreArray = array(); $words = explode(' ', Search::sanitize($expr, $id_lang)); foreach ($words as $key => $word) { if (!empty($word)) { $word = str_replace('%', '\\%', $word); $word = str_replace('_', '\\_', $word); $whereArray[] = ' p.id_product ' . ($word[0] == '-' ? 'NOT' : '') . ' IN ( SELECT 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 = ' . intval($id_lang) . ' AND sw.word LIKE ' . ($word[0] == '-' ? ' \'' . pSQL(substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)) . '%\'' : '\'' . pSQL(substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)) . '%\'') . ' ) '; if ($word[0] != '-') { $scoreArray[] = 'sw.word LIKE \'' . pSQL(substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)) . '%\''; } } else { unset($words[$key]); } } if (!sizeof($words)) { return $ajax ? array() : array('total' => 0, 'result' => array()); } $score = ''; if (sizeof($scoreArray)) { $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 = ' . intval($id_lang) . ' AND si.id_product = p.id_product AND (' . implode(' OR ', $scoreArray) . ') ) as position'; } if ($ajax) { $queryResults = ' SELECT p.id_product, pl.name as pname, IF(cl.name REGEXP "^[0-9]{2}\\.", SUBSTRING(cl.name, 4), cl.name) as cname ' . $score . ', cl.link_rewrite as crewrite, pl.link_rewrite as prewrite FROM ' . _DB_PREFIX_ . 'product p LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . intval($id_lang) . ') LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = ' . intval($id_lang) . ') WHERE ' . implode(' AND ', $whereArray) . ' AND p.active = 1 AND p.`id_product` IN ( SELECT cp.`id_product` FROM `' . _DB_PREFIX_ . 'category_group` cg LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = cg.`id_category`) WHERE cg.`id_group` ' . (!$cookie->id_customer ? '= 1' : 'IN (SELECT id_group FROM ' . _DB_PREFIX_ . 'customer_group WHERE id_customer = ' . intval($cookie->id_customer) . ')') . ' ) ORDER BY position DESC LIMIT 10'; return Db::getInstance()->ExecuteS($queryResults); } $queryResults = ' SELECT SQL_CALC_FOUND_ROWS p.*, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`, t.`rate`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name ' . $score . ' FROM ' . _DB_PREFIX_ . 'product p LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = ' . intval($id_lang) . ') LEFT OUTER JOIN `' . _DB_PREFIX_ . 'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1) LEFT JOIN `' . _DB_PREFIX_ . 'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = ' . intval($id_lang) . ') LEFT JOIN `' . _DB_PREFIX_ . 'tax` t ON (p.`id_tax` = t.`id_tax`) LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`) WHERE ' . implode(' AND ', $whereArray) . ' AND p.active = 1 AND p.`id_product` IN ( SELECT cp.`id_product` FROM `' . _DB_PREFIX_ . 'category_group` cg LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON (cp.`id_category` = cg.`id_category`) WHERE cg.`id_group` ' . (!$cookie->id_customer ? '= 1' : 'IN (SELECT id_group FROM ' . _DB_PREFIX_ . 'customer_group WHERE id_customer = ' . intval($cookie->id_customer) . ')') . ' ) ' . ($orderBy ? 'ORDER BY ' . $orderBy : '') . ($orderWay ? ' ' . $orderWay : '') . ' LIMIT ' . intval(($pageNumber - 1) * $pageSize) . ',' . intval($pageSize); $result = Db::getInstance()->ExecuteS($queryResults); $total = Db::getInstance()->getValue('SELECT FOUND_ROWS()'); Module::hookExec('search', array('expr' => $expr, 'total' => $total)); return array('total' => $total, 'result' => Product::getProductsProperties($id_lang, $result)); }
/** * @param $product_array * @param $weight_array * @param $key * @param $value * @param $id_lang * @param $iso_code */ protected static function fillProductArray(&$product_array, $weight_array, $key, $value, $id_lang, $iso_code) { if (strncmp($key, 'id_', 3) && isset($weight_array[$key])) { $words = explode(' ', Search::sanitize($value, (int) $id_lang, true, $iso_code)); foreach ($words as $word) { if (!empty($word)) { $word = Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH); if (!isset($product_array[$word])) { $product_array[$word] = 0; } $product_array[$word] += $weight_array[$key]; } } } }
private function _getProductsByNativeSearch($expr) { $db = Db::getInstance(_PS_USE_SQL_SLAVE_); $intersectArray = array(); $scoreArray = array(); $words = explode(' ', Search::sanitize($expr, (int) $this->_cookie->id_lang)); foreach ($words as $key => $word) { if (!empty($word) and strlen($word) >= (int) Configuration::get('PS_SEARCH_MINWORDLEN')) { $word = str_replace('%', '\\%', $word); $word = str_replace('_', '\\_', $word); if (version_compare(_PS_VERSION_, '1.5.0.0', '<')) { $word = Tools::replaceAccentedChars($word); } $intersectArray[] = 'SELECT 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) $this->_cookie->id_lang . (version_compare(_PS_VERSION_, '1.5.0.0', '>=') ? ' AND sw.id_shop = ' . Context::getContext()->shop->id . ' ' : '') . ' 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)) . '%\''; } } else { unset($words[$key]); } } if (!sizeof($words)) { return array(0); } $sqlGroups = ''; if (version_compare(_PS_VERSION_, '1.6.0.0', '>=') && Group::isFeatureActive() && version_compare(_PS_VERSION_, '1.6.0.0', '<')) { $currentGroups = FrontController::getCurrentCustomerGroups(); $sqlGroups = 'AND cg.`id_group` ' . (is_array($currentGroups) && sizeof($currentGroups) ? 'IN (' . implode(',', $currentGroups) . ')' : '= 1'); } $result = $db->ExecuteS(' SELECT DISTINCT cp.`id_product` FROM `' . _DB_PREFIX_ . 'category_product` cp ' . (version_compare(_PS_VERSION_, '1.6.0.0', '>=') && Group::isFeatureActive() && version_compare(_PS_VERSION_, '1.6.0.0', '<') ? '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` ' . (version_compare(_PS_VERSION_, '1.5.0.0', '>=') ? Shop::addSqlAssociation('product', 'p', false) : '') . ' WHERE c.`active` = 1 ' . (version_compare(_PS_VERSION_, '1.5.0.0', '>=') ? ' AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "search") AND product_shop.indexed = 1 ' : ' AND p.`active` = 1 AND indexed = 1 ') . $sqlGroups, false); $eligibleProducts = array(); while ($row = $db->nextRow($result)) { $eligibleProducts[] = (int) $row['id_product']; } foreach ($intersectArray as $query) { $result = $db->ExecuteS($query, false); $eligibleProducts2 = array(); while ($row = $db->nextRow($result)) { $eligibleProducts2[] = (int) $row['id_product']; } $eligibleProducts = array_intersect($eligibleProducts, $eligibleProducts2); if (!count($eligibleProducts)) { return array(0); } } array_unique($eligibleProducts); if (sizeof($eligibleProducts)) { return $eligibleProducts; } else { return array(0); } }
public static function indexation($full = false) { $db = Db::getInstance(); if ($full) { $db->Execute('TRUNCATE ' . _DB_PREFIX_ . 'search_index'); $db->Execute('TRUNCATE ' . _DB_PREFIX_ . 'search_word'); $db->Execute('UPDATE ' . _DB_PREFIX_ . 'product SET indexed = 0'); } else { $products = $db->ExecuteS('SELECT id_product FROM ' . _DB_PREFIX_ . 'product WHERE indexed = 0'); $ids = array(); if ($products) { foreach ($products as $product) { $ids[] = (int) $product['id_product']; } } if (sizeof($ids)) { $db->Execute('DELETE FROM ' . _DB_PREFIX_ . 'search_index WHERE id_product IN (' . implode(',', $ids) . ')'); } } $weightArray = 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')); $products = $db->ExecuteS(' SELECT p.id_product, pl.id_lang, pl.name pname, p.reference, p.ean13, p.upc, pl.description_short, pl.description, cl.name cname, m.name mname FROM ' . _DB_PREFIX_ . 'product p LEFT JOIN ' . _DB_PREFIX_ . 'product_lang pl ON p.id_product = pl.id_product LEFT JOIN ' . _DB_PREFIX_ . 'category_lang cl ON (cl.id_category = p.id_category_default AND pl.id_lang = cl.id_lang) LEFT JOIN ' . _DB_PREFIX_ . 'manufacturer m ON m.id_manufacturer = p.id_manufacturer WHERE p.indexed = 0', false); $countWords = 0; $countProducts = 0; $queryArray = array(); $queryArray2 = array(); $productsArray = array(); while ($product = $db->nextRow($products)) { $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']); $pArray = array(); foreach ($product as $key => $value) { if (strncmp($key, 'id_', 3)) { $words = explode(' ', Search::sanitize($value, (int) $product['id_lang'], true)); foreach ($words as $word) { if (!empty($word)) { $word = Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH); if (!isset($pArray[$word])) { $pArray[$word] = $weightArray[$key]; } else { $pArray[$word] += $weightArray[$key]; } } } } } foreach ($pArray as $word => $weight) { if (!$weight) { continue; } $queryArray[] = '(' . (int) $product['id_lang'] . ',\'' . pSQL($word) . '\')'; $queryArray2[] = '(' . (int) $product['id_product'] . ',(SELECT id_word FROM ' . _DB_PREFIX_ . 'search_word WHERE word = \'' . pSQL($word) . '\' AND id_lang = ' . (int) $product['id_lang'] . ' LIMIT 1),' . (int) $weight . ')'; // Force save every 40 words in order to avoid overloading MySQL if (++$countWords % 40 == 0) { Search::saveIndex($queryArray, $queryArray2); } } if (!in_array($product['id_product'], $productsArray)) { $productsArray[] = (int) $product['id_product']; } // Force save every 20 products in order to avoid overloading MySQL if (++$countProducts % 20 == 0) { Search::setProductsAsIndexed($productsArray); $productsArray = array(); } } // One last save is done at the end in order to save what's left Search::saveIndex($queryArray, $queryArray2); Search::setProductsAsIndexed($productsArray); $db->Execute('DELETE FROM ' . _DB_PREFIX_ . 'search_word WHERE id_word NOT IN (SELECT id_word FROM ' . _DB_PREFIX_ . 'search_index)'); return true; }