Jump to content

Show best sellers products by category in product-listing page


Recommended Posts

hi ,

 

anyone have idea about this topic ? i was trying listing the best seller product in product listing page but its doest working ! :(( if any one have idea about it please share me !

thanks all

Link to comment
Share on other sites

Hi there,

 

Nasically, you first need a module that hooks into a category. After having this, in the hookCategory (or whatever the name) function, be sure you copy and adapt most of the content of the method Product::getBestSales. Now, inside that function, add this simple condition

 

AND cp.id_category = (int)Tools::getValue('id_category')

 

Indeed, you might want to separately validate the category id first :)

  • Like 1
Link to comment
Share on other sites

  • 1 year later...

Nemo thanks for your replay , i got a module for showing the best sellers products by category wise :) anyway i am really thankful for your replay mate ! and add a new hook for category page

http://www.prestasho...rding-to-sales/

 

Did this module work for displaying Best Sellers per Category on it's own page? Also, do you know if it works with the new version of PS? (1.6.0.8)

Link to comment
Share on other sites

  • 4 months later...

Hi there,

 

Nasically, you first need a module that hooks into a category. After having this, in the hookCategory (or whatever the name) function, be sure you copy and adapt most of the content of the method Product::getBestSales. Now, inside that function, add this simple condition

 

AND cp.id_category = (int)Tools::getValue('id_category')

 

Indeed, you might want to separately validate the category id first :)

hi you

thanks for your cmt but I was write like this ,it's had worked

Edited by john smith151 (see edit history)
Link to comment
Share on other sites

  • 4 weeks later...

Hi,
 
For Prestashop 1.6 users, just go to class/productsale.php and replace this part (line 160-164)

//Subquery: get product ids in a separate query to (greatly!) improve performances and RAM usage
$products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT cp.`id_product`
FROM `'._DB_PREFIX_.'category_product` cp
LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON (cg.`id_category` = cp.`id_category`)
WHERE cg.`id_group` '.$sql_groups);

by this new part :

//Subquery: get product ids in a separate query to (greatly!) improve performances and RAM usage
$products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
SELECT cp.`id_product`
FROM `'._DB_PREFIX_.'category_product` cp
LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON (cg.`id_category` = cp.`id_category`)
WHERE cg.`id_group` '.$sql_groups.' AND cp.id_category = '.(int)Tools::getValue('id_category'));

Then, create a new hook in your category page.

Link to comment
Share on other sites

  • 1 month later...

Hello,
  
Replace the function getBestSales in classes/productsale.php by this one

 
 

	public static function getBestSales($id_lang, $page_number = 0, $nb_products = 10, $order_by = null, $order_way = null) {
		if ($page_number < 0) $page_number = 0;
		if ($nb_products < 1) $nb_products = 10;
		$final_order_by = $order_by;
		$order_table = ''; 		
		if (is_null($order_by) || $order_by == 'position' || $order_by == 'price') $order_by = 'sales';
		if ($order_by == 'date_add' || $order_by == 'date_upd')
			$order_table = 'product_shop'; 				
		if (is_null($order_way) || $order_by == 'sales') $order_way = 'DESC';

		$sql_groups = '';
		if (Group::isFeatureActive())
		{
			$groups = FrontController::getCurrentCustomerGroups();
			$sql_groups = 'WHERE cp.`id_product` IS NOT NULL AND cg.`id_group` '.(count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1');
		}
		$interval = Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20;

		// Subquery: get product ids in a separate query to (greatly!) improve performances and RAM usage
		$products = Db::getInstance(_PS_USE_SQL_SLAVE_)->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`)
		'.$sql_groups);
	
		$ids = array();
		foreach ($products as $product)
			if (Validate::isUnsignedId($product['id_product']))
				$ids[$product['id_product']] = 1;
		$ids = array_keys($ids);
		$ids = array_filter($ids);
		sort($ids);
		$ids = count($ids) > 0 ? implode(',', $ids) : 'NULL';
		
		//Main query
		$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`,
					m.`name` AS manufacturer_name, p.`id_manufacturer` as id_manufacturer,
					MAX(image_shop.`id_image`) id_image, il.`legend`,
					ps.`quantity` AS sales, t.`rate`, pl.`meta_keywords`, pl.`meta_title`, pl.`meta_description`,
					DATEDIFF(p.`date_add`, DATE_SUB(NOW(),
					INTERVAL '.$interval.' DAY)) > 0 AS new
				FROM `'._DB_PREFIX_.'product_sale` ps
				LEFT JOIN `'._DB_PREFIX_.'product` p ON ps.`id_product` = p.`id_product`
				'.Shop::addSqlAssociation('product', 'p', false).'
				LEFT 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_.'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_.'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
				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` t ON (t.`id_tax` = tr.`id_tax`)
				'.Product::sqlStock('p').'
				WHERE product_shop.`active` = 1
					AND p.`visibility` != \'none\'
					AND p.`id_product` IN ('.$ids.')
				GROUP BY product_shop.id_product
				ORDER BY '.(!empty($order_table) ? '`'.pSQL($order_table).'`.' : '').'`'.pSQL($order_by).'` '.pSQL($order_way).'
				LIMIT '.(int)($page_number * $nb_products).', '.(int)$nb_products;

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

		if ($final_order_by == 'price')
			Tools::orderbyPrice($result, $order_way);
		if (!$result)
			return false;
		return Product::getProductsProperties($id_lang, $result);
	}

Then, create the hook. There are many subjects that explain how to create a hook.

Link to comment
Share on other sites

Ok i was wrong

 

Change the GetbestSaleslight function instead of GetbestSales by this new function. It should work

public static function getBestSalesLight($id_lang, $page_number = 0, $nb_products = 10, Context $context = null)
	{
		if (!$context)
			$context = Context::getContext();
		if ($page_number < 0) $page_number = 0;
		if ($nb_products < 1) $nb_products = 10;

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

		//Subquery: get product ids in a separate query to (greatly!) improve performances and RAM usage
		$products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
		SELECT cp.`id_product`
		FROM `'._DB_PREFIX_.'category_product` cp
		LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON (cg.`id_category` = cp.`id_category`)
		WHERE cg.`id_group` '.$sql_groups.' AND cp.id_category = '.(int)Tools::getValue('id_category'));
		
		$ids = array();
		foreach ($products as $product)
			$ids[$product['id_product']] = 1;

		$ids = array_keys($ids);		
		sort($ids);
		$ids = count($ids) > 0 ? implode(',', $ids) : 'NULL';

		//Main query
		$sql = '
		SELECT
			p.id_product,  MAX(product_attribute_shop.id_product_attribute) id_product_attribute, pl.`link_rewrite`, pl.`name`, pl.`description_short`, product_shop.`id_category_default`,
			MAX(image_shop.`id_image`) id_image, il.`legend`,
			ps.`quantity` AS sales, p.`ean13`, p.`upc`, cl.`link_rewrite` AS category, p.show_price, p.available_for_order, IFNULL(stock.quantity, 0) as quantity, p.customizable,
			IFNULL(pa.minimal_quantity, p.minimal_quantity) as minimal_quantity, stock.out_of_stock,
			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, product_shop.`on_sale`
		FROM `'._DB_PREFIX_.'product_sale` ps
		LEFT JOIN `'._DB_PREFIX_.'product` p ON ps.`id_product` = p.`id_product`
		'.Shop::addSqlAssociation('product', 'p').'
		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_.'product_lang` pl
			ON p.`id_product` = pl.`id_product`
			AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').'
		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_.'category_lang` cl
			ON cl.`id_category` = product_shop.`id_category_default`
			AND cl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('cl').'
		WHERE product_shop.`active` = 1
		AND p.`visibility` != \'none\'
		AND p.`id_product` IN ('.$ids.')
		GROUP BY product_shop.id_product
		ORDER BY sales DESC
		LIMIT '.(int)($page_number * $nb_products).', '.(int)$nb_products;

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

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

  • Like 1
Link to comment
Share on other sites

  • 8 months later...

the pleasure is mine

 

 

 

Thank you so much. it works!

 

 

Hello,

 

How are you? Maybe you can help me with it...

Looks like working for me too.

 

Just is problem with cache. It's using cache for fist item from category. When cache is clear, item adding to cache from first category where user in.

 

When cleaning - "blockbestsellers_col" everything is okey..

 

Regards.

Link to comment
Share on other sites

×
×
  • Create New...