Jump to content

No salen las categorías en los resultados de las búsquedas

MUELAMEN

By MUELAMEN
in Discusión general

 Share

Recommended Posts

MUELAMEN Newbie

MUELAMEN

Posted
Posted

Hola,

Tengo un problema que me tiene loco.

Al hacer una búsqueda, me lista los resultados, pero todos con el campo categoría vacío.

¿Alguien sabe a que puede deberse?

Si luego accedo a la ficha de producto si sale.

Solamente me pasa en los resultados de las búsquedas.

Gracias de antemano.

post-566981-0-85482400-1372852155_thumb.jpeg

Link to comment
Share on other sites
Sergio Ruiz Newbie

Sergio Ruiz

Posted
Posted (edited)

Hola,

Tengo un problema que me tiene loco.

Al hacer una búsqueda, me lista los resultados, pero todos con el campo categoría vacío.

¿Alguien sabe a que puede deberse?

Si luego accedo a la ficha de producto si sale.

Solamente me pasa en los resultados de las búsquedas.

Gracias de antemano.

post-566981-0-85482400-1372852155_thumb.jpeg

.

 

Haz una cosilla, habilita el reporte de errores: http://www.prestasho...-en-prestashop/ como dice "Nadie" y di si al lado del campo categoria, te aparece un error.

Edited by Rubalcaba (see edit history)
  • Like 1
Link to comment
Share on other sites
MUELAMEN Newbie

MUELAMEN

Posted
  • Author
Posted (edited)

.

 

Haz una cosilla, habilita el reporte de errores: http://www.prestasho...-en-prestashop/ como dice "Nadie" y di si al lado del campo categoria, te aparece un error.

Gracias por la respuesta, y perdona mi ignmoracia, pero esto no parece ser el mismo error.

A mi no se me queda la pantalla en blanco

Edited by MUELAMEN (see edit history)
Link to comment
Share on other sites
Sergio Ruiz Newbie

Sergio Ruiz

Posted
Posted

Gracias por la respuesta, y perdona mi ignmoracia, pero esto no parece ser el mismo error.

A mi no se me queda la pantalla en blanco

Habilita el reporte de errores, habilitar el reporte de errores, no solo se hace cuando se queda la pantalla en blanco.

  • Like 1
Link to comment
Share on other sites
Sergio Ruiz Newbie

Sergio Ruiz

Posted
Posted

Ya está hecho, pero sigue igual

Um

 

Creo que es posible que el problema, venga de la funcion:

 

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_);
 // Only use cookie if id_customer is not present
 if ($use_cookie)
  $id_customer = $context->customer->id;
 else
  $id_customer = 0;
 // 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));
 foreach ($words as $key => $word)
  if (!empty($word) && strlen($word) >= (int)Configuration::get('PS_SEARCH_MINWORDLEN'))
  {
   $word = str_replace('%', '\\%', $word);
   $word = str_replace('_', '\\_', $word);
   $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] == '-'
  ? ' \''.pSQL(Tools::substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)).'%\''
  : '\''.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\''
 );
   if ($word[0] != '-')
 $score_array[] = 'sw.word LIKE \''.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\'';
  }
  else
   unset($words[$key]);
 if (!count($words))
  return ($ajax ? array() : array('total' => 0, 'result' => 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 = '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`
   '.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
 AND cg.`id_group` '.(!$id_customer ?  '= 1' : 'IN (
  SELECT id_group FROM '._DB_PREFIX_.'customer_group
  WHERE id_customer = '.(int)$id_customer.'
 )');
 $results = $db->executeS($sql);
 $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()));
 }
 $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.';
 $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.', 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
   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_.'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).'
   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);
 $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);
 return array('total' => $total,'result' => $result_properties);
}

 

en el fichero:

 

/classes/Search.php

  • Like 1
Link to comment
Share on other sites
MUELAMEN Newbie

MUELAMEN

Posted
  • Author
Posted (edited)

Um

 

Creo que es posible que el problema, venga de la funcion:

 

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_);
 // Only use cookie if id_customer is not present
 if ($use_cookie)
  $id_customer = $context->customer->id;
 else
  $id_customer = 0;
 // 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));
 foreach ($words as $key => $word)
  if (!empty($word) && strlen($word) >= (int)Configuration::get('PS_SEARCH_MINWORDLEN'))
  {
$word = str_replace('%', '\\%', $word);
$word = str_replace('_', '\\_', $word);
$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] == '-'
  ? ' \''.pSQL(Tools::substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)).'%\''
  : '\''.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\''
 );
if ($word[0] != '-')
 $score_array[] = 'sw.word LIKE \''.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\'';
  }
  else
unset($words[$key]);
 if (!count($words))
  return ($ajax ? array() : array('total' => 0, 'result' => 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 = '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`
'.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
 AND cg.`id_group` '.(!$id_customer ?  '= 1' : 'IN (
  SELECT id_group FROM '._DB_PREFIX_.'customer_group
  WHERE id_customer = '.(int)$id_customer.'
 )');
 $results = $db->executeS($sql);
 $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()));
 }
 $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.';
 $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.', 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
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_.'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).'
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);
 $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);
 return array('total' => $total,'result' => $result_properties);
}

 

en el fichero:

 

/classes/Search.php

Gracias, pero no doy con ello.

Es posible que venga de esa función, pero no encuentro el fallo

Yo tengo esto:

 

 

class SearchCore

{

public static function sanitize($string, $id_lang, $indexation = false)

{

$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

{

$string = preg_replace('/[._]+/', '', $string);

$string = ltrim(preg_replace('/([^ ])-/', '$1', ' '.$string));

$string = preg_replace('/[._]+/', '', $string);

$string = preg_replace('/[^\s]-+/', '', $string);

}

 

$blacklist = 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 (!$indexation)

{

$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);

}

 

if ($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 = 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_);

 

// Only use cookie if id_customer is not present

if ($use_cookie)

$id_customer = $context->customer->id;

else

$id_customer = 0;

 

// 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));

 

foreach ($words as $key => $word)

if (!empty($word) && strlen($word) >= (int)Configuration::get('PS_SEARCH_MINWORDLEN'))

{

$word = str_replace('%', '\\%', $word);

$word = str_replace('_', '\\_', $word);

$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] == '-'

? ' \''.pSQL(Tools::substr($word, 1, PS_SEARCH_MAX_WORD_LENGTH)).'%\''

: '\''.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\''

);

 

if ($word[0] != '-')

$score_array[] = 'sw.word LIKE \''.pSQL(Tools::substr($word, 0, PS_SEARCH_MAX_WORD_LENGTH)).'%\'';

}

else

unset($words[$key]);

 

if (!count($words))

return ($ajax ? array() : array('total' => 0, 'result' => 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 = '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`

'.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

AND cg.`id_group` '.(!$id_customer ? '= 1' : 'IN (

SELECT id_group FROM '._DB_PREFIX_.'customer_group

WHERE id_customer = '.(int)$id_customer.'

)');

$results = $db->executeS($sql);

 

$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()));

}

 

$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.';

$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`,

tax.`rate`, image_shop.`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

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_.'tax_rule` tr ON (product_shop.`id_tax_rules_group` = tr.`id_tax_rules_group`

AND tr.`id_country` = '.(int)$context->country->id.'

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`)'.

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.')

'.Product::sqlStock('p', 0).'

WHERE p.`id_product` '.$product_pool.'

AND ((image_shop.id_image IS NOT NULL OR i.id_image IS NULL) OR (image_shop.id_image IS NULL AND i.cover=1))

'.($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);

 

$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_.'tax_rule` tr ON (product_shop.`id_tax_rules_group` = tr.`id_tax_rules_group`

AND tr.`id_country` = '.(int)Context::getContext()->country->id.'

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`)'.

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.'

AND ((image_shop.id_image IS NOT NULL OR i.id_image IS NULL) OR (image_shop.id_image IS NULL AND i.cover=1))';

$total = $db->getValue($sql);

 

if (!$result)

$result_properties = false;

else

$result_properties = Product::getProductsProperties((int)$id_lang, $result);

 

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

}

 

public static function getTags($db, $id_product, $id_lang)

{

$tags = '';

$tagsArray = $db->executeS('

SELECT t.name FROM '._DB_PREFIX_.'product_tag pt

LEFT JOIN '._DB_PREFIX_.'tag t ON (pt.id_tag = t.id_tag AND t.id_lang = '.(int)$id_lang.')

WHERE pt.id_product = '.(int)$id_product);

foreach ($tagsArray as $tag)

$tags .= $tag['name'].' ';

return $tags;

}

 

public static function getAttributes($db, $id_product, $id_lang)

{

$attributes = '';

$attributesArray = $db->ExecuteS('

SELECT al.name, pa.reference FROM '._DB_PREFIX_.'product_attribute pa

INNER JOIN '._DB_PREFIX_.'product_attribute_combination pac ON pa.id_product_attribute = pac.id_product_attribute

INNER JOIN '._DB_PREFIX_.'attribute_lang al ON (pac.id_attribute = al.id_attribute AND al.id_lang = '.(int)$id_lang.')

WHERE pa.id_product = '.(int)$id_product);

foreach ($attributesArray AS $attribute)

$attributes .= $attribute['name'].' '.($attribute['reference'] != "" ? $attribute['reference'].' ' : '');

return $attributes;

}

 

public static function getFeatures($db, $id_product, $id_lang)

{

if (!Feature::isFeatureActive())

return '';

 

$features = '';

$featuresArray = $db->executeS('

SELECT fvl.value FROM '._DB_PREFIX_.'feature_product fp

LEFT JOIN '._DB_PREFIX_.'feature_value_lang fvl ON (fp.id_feature_value = fvl.id_feature_value AND fvl.id_lang = '.(int)$id_lang.')

WHERE fp.id_product = '.(int)$id_product);

foreach ($featuresArray as $feature)

$features .= $feature['value'].' ';

return $features;

}

 

protected static function getProductsToIndex($total_languages, $id_product = false, $limit = 50)

{

// Adjust the limit to get only "whole" products, in every languages (and at least one)

$max_possibilities = $total_languages * count(Shop::getShops(true));

$limit = max(1, floor($limit / $max_possibilities) * $max_possibilities);

 

return Db::getInstance()->executeS('

SELECT p.id_product, pl.id_lang, pl.id_shop, 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

'.Shop::addSqlAssociation('product', 'p').'

LEFT JOIN '._DB_PREFIX_.'category_lang cl

ON (cl.id_category = product_shop.id_category_default AND pl.id_lang = cl.id_lang AND cl.id_shop = product_shop.id_shop)

LEFT JOIN '._DB_PREFIX_.'manufacturer m

ON m.id_manufacturer = p.id_manufacturer

WHERE product_shop.indexed = 0

AND product_shop.visibility IN ("both", "search")

'.($id_product ? 'AND p.id_product = '.(int)$id_product : '').'

LIMIT '.(int)$limit

);

}

 

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), '1');

}

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).')');

}

 

// 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();

$products_array = 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))

{

// 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))

{

$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);

// 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;

}

 

protected static function setProductsAsIndexed(&$products)

{

if (count($products))

ObjectModel::updateMultishopTable('Product', array('indexed' => 1), 'a.id_product IN ('.implode(',', $products).')');

}

 

/** $queryArray3 is automatically emptied in order to be reused immediatly */

protected static function saveIndex(&$queryArray3)

{

if (count($queryArray3))

Db::getInstance()->execute(

'INSERT INTO '._DB_PREFIX_.'search_index (id_product, id_word, weight)

VALUES '.implode(',', $queryArray3).'

ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight)'

);

$queryArray3 = array();

}

 

public static function searchTag($id_lang, $tag, $count = false, $pageNumber = 0, $pageSize = 10, $orderBy = false, $orderWay = false,

$useCookie = true, Context $context = null)

{

if (!$context)

$context = Context::getContext();

 

// Only use cookie if id_customer is not present

if ($useCookie)

$id_customer = (int)$context->customer->id;

else

$id_customer = 0;

 

if (!is_numeric($pageNumber) || !is_numeric($pageSize) || !Validate::isBool($count) || !Validate::isValidSearch($tag)

|| $orderBy && !$orderWay || ($orderBy && !Validate::isOrderBy($orderBy)) || ($orderWay && !Validate::isOrderBy($orderWay)))

return false;

 

if ($pageNumber < 1) $pageNumber = 1;

if ($pageSize < 1) $pageSize = 10;

 

$id = Context::getContext()->shop->id;

$id_shop = $id ? $id : Configuration::get('PS_SHOP_DEFAULT');

if ($count)

{

$sql = 'SELECT COUNT(DISTINCT pt.`id_product`) nb

FROM `'._DB_PREFIX_.'product` p

'.Shop::addSqlAssociation('product', 'p').'

LEFT JOIN `'._DB_PREFIX_.'product_tag` pt ON (p.`id_product` = pt.`id_product`)

LEFT JOIN `'._DB_PREFIX_.'tag` t ON (pt.`id_tag` = t.`id_tag` AND t.`id_lang` = '.(int)$id_lang.')

LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)

LEFT JOIN `'._DB_PREFIX_.'category_shop` cs ON (cp.`id_category` = cs.`id_category` AND cs.`id_shop` = '.(int)$id_shop.')

LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON (cg.`id_category` = cp.`id_category`)

WHERE product_shop.`active` = 1

AND cs.`id_shop` = '.(int)Context::getContext()->shop->id.'

AND cg.`id_group` '.(!$id_customer ? '= 1' : 'IN (

SELECT id_group FROM '._DB_PREFIX_.'customer_group

WHERE id_customer = '.(int)$id_customer.')').'

AND t.`name` LIKE \'%'.pSQL($tag).'%\'';

return (int)Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue($sql);

}

 

$sql = 'SELECT DISTINCT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description_short`, pl.`link_rewrite`, pl.`name`,

tax.`rate`, image_shop.`id_image`, il.`legend`, m.`name` manufacturer_name, 1 position,

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

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', false).'

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.')

LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (product_shop.`id_tax_rules_group` = tr.`id_tax_rules_group`

AND tr.`id_country` = '.(int)$context->country->id.'

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_.'product_tag` pt ON (p.`id_product` = pt.`id_product`)

LEFT JOIN `'._DB_PREFIX_.'tag` t ON (pt.`id_tag` = t.`id_tag` AND t.`id_lang` = '.(int)$id_lang.')

LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_product` = p.`id_product`)

LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON (cg.`id_category` = cp.`id_category`)

LEFT JOIN `'._DB_PREFIX_.'category_shop` cs ON (cg.`id_category` = cs.`id_category` AND cs.`id_shop` = '.(int)$id_shop.')

'.Product::sqlStock('p', 0).'

WHERE product_shop.`active` = 1

AND cs.`id_shop` = '.(int)Context::getContext()->shop->id.'

AND cg.`id_group` '.(!$id_customer ? '= 1' : 'IN (

SELECT id_group FROM '._DB_PREFIX_.'customer_group

WHERE id_customer = '.(int)$id_customer.')').'

AND t.`name` LIKE \'%'.pSQL($tag).'%\'

AND ((image_shop.id_image IS NOT NULL OR i.id_image IS NULL) OR (image_shop.id_image IS NULL AND i.cover=1))

ORDER BY position DESC'.($orderBy ? ', '.$orderBy : '').($orderWay ? ' '.$orderWay : '').'

LIMIT '.(int)(($pageNumber - 1) * $pageSize).','.(int)$pageSize;

if (!$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql))

return false;

 

return Product::getProductsProperties((int)$id_lang, $result);

}

}

Edited by MUELAMEN (see edit history)
Link to comment
Share on other sites
Guest
This topic is now closed to further replies.
 Share
Go to topic listing
×
×
  • Create New...