(SOLUCIONADO) Cambiar consulta SQL de productos "NUEVOS"

[kobokila]

Saludos¡¡

quiero cambiar los productos que salen en la portada y sabría hacerlo cambiando la consulta SQL

que se ejecuta para encontrar esos productos, alguien sabría decirme donde puedo encontrar tal consulta?

 

gracias

 

PD:Versión 1.6

Edited June 2, 2014 by kobokila (see edit history)

[kobokila]

Up

 

Sé que es una pregunta "compleja" pero no creo que tanto xD

lo que quiero es editar esa consulta para poder elegir yo mediante esa que productos mostrar en la portada (aleatorios y en oferta, por ejemplo)

[kobokila]

Up

 

He encontrado que por la carpeta classes/product.php, hay muchas consultas y no se si es alguna de ellas y de serlo, cual podria ser >.<

alguna idea?¿ 

[kobokila]

Up

 

Juraria que la consulta que debo editar es la siguiente:

 

public static function getProducts($id_lang, $start, $limit, $order_by, $order_way, $id_category = false,

$only_active = false, Context $context = null)

{

if (!$context)

$context = Context::getContext();

 

$front = true;

if (!in_array($context->controller->controller_type, array('front', 'modulefront')))

$front = false;

 

if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way))

die (Tools::displayError());

if ($order_by == 'id_product' || $order_by == 'price' || $order_by == 'date_add' || $order_by == 'date_upd')

$order_by_prefix = 'p';

else if ($order_by == 'name')

$order_by_prefix = 'pl';

else if ($order_by == 'position')

$order_by_prefix = 'c';

 

if (strpos($order_by, '.') > 0)

{

$order_by = explode('.', $order_by);

$order_by_prefix = $order_by[0];

$order_by = $order_by[1];

}

$sql = 'SELECT p.*, product_shop.*, pl.* , m.`name` AS manufacturer_name, s.`name` AS supplier_name

FROM `'._DB_PREFIX_.'product` p

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

LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product` '.Shop::addSqlRestrictionOnLang('pl').')

LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)

LEFT JOIN `'._DB_PREFIX_.'supplier` s ON (s.`id_supplier` = p.`id_supplier`)'.

($id_category ? 'LEFT JOIN `'._DB_PREFIX_.'category_product` c ON (c.`id_product` = p.`id_product`)' : '').'

WHERE pl.`id_lang` = '.(int)$id_lang.

($id_category ? ' AND c.`id_category` = '.(int)$id_category : '').

($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '').

($only_active ? ' AND product_shop.`active` = 1' : '').'

ORDER BY '.(isset($order_by_prefix) ? pSQL($order_by_prefix).'.' : '').'`'.pSQL($order_by).'` '.pSQL($order_way).

($limit > 0 ? ' LIMIT '.(int)$start.','.(int)$limit : '');

 

$rq = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);

if ($order_by == 'price')

Tools::orderbyPrice($rq, $order_way);

 

foreach ($rq as &$row)

$row = Product::getTaxesInformations($row);

 

return ($rq);

}

 

alguna forma de comprobarlo?

[kobokila]

Up 

 

nadie sabe absolutamente nada ?? xDD ese fichero acabó por no ser T.T

ideaS?

[ventura]

No se que quieres hacer exactamente con esa consulta de la classe, al ser novedades o productos nuevos el parametro fundamental es la fecha en que se agrega tal producto, si quieres cambiar los productos lo puedes hacer desde la base de datos editando la fecha o realizando una consulta para hacerlo masivo

[galindogadea]

Si comentas que es lo que quieres hacer exactamente, te podremos ayudar.

 

Es decir en base a que quieres mostrar los productos ¿Sera en base a un criterio? ¿no?

Edited April 1, 2014 by galindogadea (see edit history)

[kobokila]

Quiero encontrar la consulta que usa para por ejemplo, poder mostrar de la misma forma que hace con los nuevos, mostrar los productos en promocion, o productos dentro de un rango de tiempo

[kobokila]

Por ejemplo ahora ando intentando que salgan ofertas, que queden como quedan los nuevos o los populares, pero que solo se muestren las ofertas 

 

EDIT: Mejor mantengo la pregunta de como cambiar la consulta del SQL y abro una nueva pregunta en el foro par ano liar 

Edited April 1, 2014 by kobokila (see edit history)

[cirrus1]

Hola supongo que será esta función la que buscas:

 

public static function getNewProducts($id_lang, $page_number = 0, $nb_products = 10,

$count = false, $order_by = null, $order_way = null, Context $context = null)

{

if (!$context)

$context = Context::getContext();

 

$front = true;

if (!in_array($context->controller->controller_type, array('front', 'modulefront')))

$front = false;

 

if ($page_number < 0) $page_number = 0;

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

if (empty($order_by) || $order_by == 'position') $order_by = 'date_add';

if (empty($order_way)) $order_way = 'DESC';

if ($order_by == 'id_product' || $order_by == 'price' || $order_by == 'date_add'  || $order_by == 'date_upd')

$order_by_prefix = 'p';

else if ($order_by == 'name')

$order_by_prefix = 'pl';

if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way))

die(Tools::displayError());

 

$groups = FrontController::getCurrentCustomerGroups();

$sql_groups = (count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1');

if (strpos($order_by, '.') > 0)

{

$order_by = explode('.', $order_by);

$order_by_prefix = $order_by[0];

$order_by = $order_by[1];

}

if ($count)

{

$sql = 'SELECT COUNT(p.`id_product`) AS nb

FROM `'._DB_PREFIX_.'product` p

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

WHERE product_shop.`active` = 1

AND product_shop.`date_add` > "'.date('Y-m-d', strtotime('-'.(Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? (int)Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY')).'"

'.($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '').'

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` '.$sql_groups.'

)';

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

}

 

$sql = new DbQuery();

$sql->select(

'p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`,

pl.`meta_keywords`, pl.`meta_title`, pl.`name`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` AS manufacturer_name,

product_shop.`date_add` > "'.date('Y-m-d', strtotime('-'.(Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? (int)Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY')).'" as new'

);

 

$sql->from('product', 'p');

$sql->join(Shop::addSqlAssociation('product', 'p'));

$sql->leftJoin('product_lang', 'pl', '

p.`id_product` = pl.`id_product`

AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl')

);

$sql->leftJoin('image', 'i', 'i.`id_product` = p.`id_product`');

$sql->join(Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1'));

$sql->leftJoin('image_lang', 'il', 'i.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$id_lang);

$sql->leftJoin('manufacturer', 'm', 'm.`id_manufacturer` = p.`id_manufacturer`');

 

$sql->where('product_shop.`active` = 1');

if ($front)

$sql->where('product_shop.`visibility` IN ("both", "catalog")');

$sql->where('product_shop.`date_add` > "'.date('Y-m-d', strtotime('-'.(Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? (int)Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY')).'"');

$sql->where('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` '.$sql_groups.'

)');

$sql->groupBy('product_shop.id_product');

 

$sql->orderBy((isset($order_by_prefix) ? pSQL($order_by_prefix).'.' : '').'`'.pSQL($order_by).'` '.pSQL($order_way));

$sql->limit($nb_products, $page_number * $nb_products);

 

if (Combination::isFeatureActive())

{

$sql->select('MAX(product_attribute_shop.id_product_attribute) id_product_attribute');

$sql->leftOuterJoin('product_attribute', 'pa', 'p.`id_product` = pa.`id_product`');

$sql->join(Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.default_on = 1'));

}

$sql->join(Product::sqlStock('p', Combination::isFeatureActive() ? 'product_attribute_shop' : 0));

 

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

 

if ($order_by == 'price')

Tools::orderbyPrice($result, $order_way);

if (!$result)

return false;

 

$products_ids = array();

foreach ($result as $row)

$products_ids[] = $row['id_product'];

// Thus you can avoid one query per product, because there will be only one query for all the products of the cart

Product::cacheFrontFeatures($products_ids, $id_lang);

 

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

}

 

// si buscas Get new products en la clase product.php ahi está

//Saludos

[kobokila]

 

Hola supongo que será esta función la que buscas:

 

public static function getNewProducts($id_lang, $page_number = 0, $nb_products = 10,

$count = false, $order_by = null, $order_way = null, Context $context = null)

{

if (!$context)

$context = Context::getContext();

 

$front = true;

if (!in_array($context->controller->controller_type, array('front', 'modulefront')))

$front = false;

 

if ($page_number < 0) $page_number = 0;

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

if (empty($order_by) || $order_by == 'position') $order_by = 'date_add';

if (empty($order_way)) $order_way = 'DESC';

if ($order_by == 'id_product' || $order_by == 'price' || $order_by == 'date_add'  || $order_by == 'date_upd')

$order_by_prefix = 'p';

else if ($order_by == 'name')

$order_by_prefix = 'pl';

if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way))

die(Tools::displayError());

 

$groups = FrontController::getCurrentCustomerGroups();

$sql_groups = (count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1');

if (strpos($order_by, '.') > 0)

{

$order_by = explode('.', $order_by);

$order_by_prefix = $order_by[0];

$order_by = $order_by[1];

}

if ($count)

{

$sql = 'SELECT COUNT(p.`id_product`) AS nb

FROM `'._DB_PREFIX_.'product` p

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

WHERE product_shop.`active` = 1

AND product_shop.`date_add` > "'.date('Y-m-d', strtotime('-'.(Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? (int)Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY')).'"

'.($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '').'

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` '.$sql_groups.'

)';

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

}

 

$sql = new DbQuery();

$sql->select(

'p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`,

pl.`meta_keywords`, pl.`meta_title`, pl.`name`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` AS manufacturer_name,

product_shop.`date_add` > "'.date('Y-m-d', strtotime('-'.(Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? (int)Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY')).'" as new'

);

 

$sql->from('product', 'p');

$sql->join(Shop::addSqlAssociation('product', 'p'));

$sql->leftJoin('product_lang', 'pl', '

p.`id_product` = pl.`id_product`

AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl')

);

$sql->leftJoin('image', 'i', 'i.`id_product` = p.`id_product`');

$sql->join(Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1'));

$sql->leftJoin('image_lang', 'il', 'i.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$id_lang);

$sql->leftJoin('manufacturer', 'm', 'm.`id_manufacturer` = p.`id_manufacturer`');

 

$sql->where('product_shop.`active` = 1');

if ($front)

$sql->where('product_shop.`visibility` IN ("both", "catalog")');

$sql->where('product_shop.`date_add` > "'.date('Y-m-d', strtotime('-'.(Configuration::get('PS_NB_DAYS_NEW_PRODUCT') ? (int)Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY')).'"');

$sql->where('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` '.$sql_groups.'

)');

$sql->groupBy('product_shop.id_product');

 

$sql->orderBy((isset($order_by_prefix) ? pSQL($order_by_prefix).'.' : '').'`'.pSQL($order_by).'` '.pSQL($order_way));

$sql->limit($nb_products, $page_number * $nb_products);

 

if (Combination::isFeatureActive())

{

$sql->select('MAX(product_attribute_shop.id_product_attribute) id_product_attribute');

$sql->leftOuterJoin('product_attribute', 'pa', 'p.`id_product` = pa.`id_product`');

$sql->join(Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.default_on = 1'));

}

$sql->join(Product::sqlStock('p', Combination::isFeatureActive() ? 'product_attribute_shop' : 0));

 

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

 

if ($order_by == 'price')

Tools::orderbyPrice($result, $order_way);

if (!$result)

return false;

 

$products_ids = array();

foreach ($result as $row)

$products_ids[] = $row['id_product'];

// Thus you can avoid one query per product, because there will be only one query for all the products of the cart

Product::cacheFrontFeatures($products_ids, $id_lang);

 

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

}

 

// si buscas Get new products en la clase product.php ahi está

//Saludos

 

Exacto, muchas gracias