[SOLVED] Show number of products next to categories

[DylzEn]

Hi everyone. I know this has been asked multiple times but I can't see to find a solution even after days of searching. I would love to get help on how to show the sum of the products that are in subcategories next to a parent category, like this for example:

 

- Apple Products (3) // main category

- iPod (1) // subcategory

- iPod Nano (1) //sub-subcategory

- iPhone (2) // subcategory

- iPhone 4 (1) //sub-subcategory

- iPhone 5 (1) //sub-subcategory

 

So far, following hints from these forums, I managed to get to show the correct number of products only for the categories or subcategories that actually have products in it. That is, if I have 1 iPhone 4 and 1 iPod Nano in my store, the tree is shown like this:

 

- Apple Products (0) // main category

- iPod (0) // subcategory

- iPod Nano (1) //sub-subcategory

- iPhone (0) // subcategory

- iPhone 4 (1) //sub-subcategory

- iPhone 5 (0) //sub-subcategory

 

So, the problem is I'd like to show the sum of all the products that are in the subcategories next to the main parent category, instead of that zero. I don't know if I made myself clear, and sorry for my english as it's not my first language. Tell me if you need me to post the content of any file for better understanding. I hope you guys can help me out with this and I thank you for your time.

 

Best regards,

Dylan

Edited October 2, 2012 by DylzEn (see edit history)

[Andrew R]

Override the class Category.php and put some of the following code in the file:

 

class Category extends CategoryCore{

public $recursive_categories = array();

public function getSubCategoriesOfCategoryID($category_id, $id_lang=1, $active = true)
{
  if (!Validate::isBool($active))
die(Tools::displayError());
 $groups = FrontController::getCurrentCustomerGroups();
 $sqlGroups = (count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1');
 $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
 SELECT c.*, cl.id_lang, cl.name, cl.description, cl.link_rewrite, cl.meta_title, cl.meta_keywords, cl.meta_description
 FROM `'._DB_PREFIX_.'category` c
 LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (c.`id_category` = cl.`id_category` AND `id_lang` = '.(int)($id_lang).')
 LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON (cg.`id_category` = c.`id_category`)
 WHERE `id_parent` = '.(int)($category_id).'
 '.($active ? 'AND `active` = 1' : '').'
 AND cg.`id_group` '.$sqlGroups.'
 GROUP BY c.`id_category`
 ORDER BY `level_depth` ASC, c.`position` ASC');

 foreach ($result AS &$row)
 {
  $row['id_image'] = (file_exists(_PS_CAT_IMG_DIR_.$row['id_category'].'.jpg')) ? (int)($row['id_category']) : Language::getIsoById($id_lang).'-default';
  $row['legend'] = 'no picture';
 }
 return $result;
}

public function getSubCategoriesRecursive($category_id){
 $subcategory_result = $this->getSubCategoriesOfCategoryID((int)($category_id));
 if(sizeof($subcategory_result)>0){
  foreach($subcategory_result as $subcat){
$this->recursive_categories[] = $subcat['id_category'];
$this->getSubCategoriesRecursive($subcat['id_category']);
  }
 }
 return false;
}

public function getProducts($id_lang, $p, $n, $orderBy = NULL, $orderWay = NULL, $getTotal = false, $active = true, $random = false, $randomNumberProducts = 1, $checkAccess = true)
{
 global $cookie;
 if (!$checkAccess OR !$this->checkAccess($cookie->id_customer))
  return false;
 if ($p < 1) $p = 1;
 if (empty($orderBy))
  $orderBy = 'position';
 else
  /* Fix for all modules which are now using lowercase values for 'orderBy' parameter */
  $orderBy = strtolower($orderBy);
 if (empty($orderWay))
  $orderWay = 'ASC';
 if ($orderBy == 'id_product' OR $orderBy == 'date_add')
  $orderByPrefix = 'p';
 elseif ($orderBy == 'name')
  $orderByPrefix = 'pl';
 elseif ($orderBy == 'manufacturer')
 {
  $orderByPrefix = 'm';
  $orderBy = 'name';
 }
 elseif ($orderBy == 'position')
  $orderByPrefix = 'cp';
 if ($orderBy == 'price')
  $orderBy = 'orderprice';
 if (!Validate::isBool($active) OR !Validate::isOrderBy($orderBy) OR !Validate::isOrderWay($orderWay))
  die (Tools::displayError());
 $id_supplier = (int)(Tools::getValue('id_supplier'));

 // GET ALL CHILD CATEGORY NODES OF THIS CATEGORY

 $subcategory_result = $this->getSubCategoriesOfCategoryID((int)($this->id));

 $this->recursive_categories[] = (int)($this->id);
 $this->getSubCategoriesRecursive((int)($this->id));

 $combined_categories = $this->recursive_categories;

 /* Return only the number of products */
 if ($getTotal)
 {
  $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow('
  SELECT COUNT(DISTINCT cp.`id_product`) AS total
  FROM `'._DB_PREFIX_.'product` p
  LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON p.`id_product` = cp.`id_product`
  WHERE cp.`id_category` IN ('.implode(',',$combined_categories).')'.($active ? ' AND p.`active` = 1' : '').'
  '.($id_supplier ? 'AND p.id_supplier = '.(int)($id_supplier) : ''));
  return isset($result) ? $result['total'] : 0;
 }
 $sql = '
 SELECT DISTINCT p.*, pa.`id_product_attribute`, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`, cl.`name` AS category_default, 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 AS new,
  (p.`price` * IF(t.`rate`,((100 + (t.`rate`))/100),1)) AS orderprice
 FROM `'._DB_PREFIX_.'category_product` cp
 LEFT JOIN `'._DB_PREFIX_.'product` p ON p.`id_product` = cp.`id_product`
 LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1)
 LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = '.(int)($id_lang).')
 LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = '.(int)($id_lang).')
 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).')
 LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
										 AND tr.`id_country` = '.(int)Country::getDefaultCountryId().'
											AND tr.`id_state` = 0)
 LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = tr.`id_tax`)
 LEFT JOIN `'._DB_PREFIX_.'tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = '.(int)($id_lang).')
 LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
 WHERE cp.`id_category` IN ('.implode(',',$combined_categories).')'.($active ? ' AND p.`active` = 1' : '').'
 '.($id_supplier ? 'AND p.id_supplier = '.(int)$id_supplier : '');
 if ($random === true)
 {
  $sql .= ' ORDER BY RAND()';
  $sql .= ' LIMIT 0, '.(int)($randomNumberProducts);
 }
 else
 {
  $sql .= ' ORDER BY '.(isset($orderByPrefix) ? $orderByPrefix.'.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).'
  LIMIT '.(((int)($p) - 1) * (int)($n)).','.(int)($n);
 }
 $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($sql);
 if ($orderBy == 'orderprice')
  Tools::orderbyPrice($result, $orderWay);
 if (!$result)
  return false;

 /* Modify SQL result */
 return Product::getProductsProperties($id_lang, $result);
}

}

 

That code will actually make each category you visit display all the products in subcategories below it i.e if you visit Apple Products, it will list all iPods and iPhones etc together. If you need to just get a product count, use the database statement with the count in it and assign that to a variable you can read in the .tpl file and revert the rest of the code to the same as the code in the original Prestashop Category.php file.

 

Using the above code, the number is stored in {$nb_products} when you visit a certain category. You will need a lookup to show the counts for each category.

Edited September 25, 2012 by Andrew R (see edit history)

[DylzEn]

Well, first of all thank you very much for your answer. Second of all, unfortunatley I'm a newbie here so I'm having a little bit of trouble understanding what to do. I'm using PS v1.4.7.0 and by overriding Category.php you mean creating a file in /override/classes named Category.php with the content you just posted? And no, I don't need to display every subcategory product in the main category because I will have hundreds of products for every subcategory so it would be confusing. So I just need to get a product count as you mentioned, so could you explain to me do I use the code you posted to do that without modifying anything else in my store? Thanks again for your time, I really appreciate it.

 

Dylan

[Andrew R]

I'm using PS v1.4.7.0 and by overriding Category.php you mean creating a file in /override/classes named Category.php with the content you just posted? And no, I don't need to display every subcategory product in the main category because I will have hundreds of products for every subcategory so it would be confusing. So I just need to get a product count as you mentioned, so could you explain to me do I use the code you posted to do that without modifying anything else in my store?

 

You'd be best putting some of the above code near the function that generates your tree, which might not require overriding Category.php but that is what I meant with the override - putting the class in the override folder. What code are you using to generate the following?:

 

- Apple Products (0) // main category

- iPod (0) // subcategory

- iPod Nano (1) //sub-subcategory

- iPhone (0) // subcategory

- iPhone 4 (1) //sub-subcategory

- iPhone 5 (0) //sub-subcategory

 

Is that just generated from a standard Prestashop .tpl file?

Edited September 25, 2012 by Andrew R (see edit history)

[DylzEn]

What code are you using to generate the following?:

 

- Apple Products (0) // main category

- iPod (0) // subcategory

- iPod Nano (1) //sub-subcategory

- iPhone (0) // subcategory

- iPhone 4 (1) //sub-subcategory

- iPhone 5 (0) //sub-subcategory

 

Is that just generated from a standard Prestashop .tpl file?

 

No, I modified the files "blockategories.php" (the getTree function) and "category-tree-branch.tpl" located in "/modules/blockcategories" exactly as in this post: http://www.prestashop.com/forums/index.php?/topic/68116-show-number-of-products-next-to-categories/page__view__findpost__p__771190

 

Thanks again for your time.

 

Dylan

[Andrew R]

In that case, you'd paste the following two functions above getTree in blockcategories.php:

 

public function getSubCategoriesOfCategoryID($category_id, $id_lang=1, $active = true)
{
  if (!Validate::isBool($active))
    die(Tools::displayError());
 $groups = FrontController::getCurrentCustomerGroups();
 $sqlGroups = (count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1');
 $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
 SELECT c.*, cl.id_lang, cl.name, cl.description, cl.link_rewrite, cl.meta_title, cl.meta_keywords, cl.meta_description
 FROM `'._DB_PREFIX_.'category` c
 LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (c.`id_category` = cl.`id_category` AND `id_lang` = '.(int)($id_lang).')
 LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON (cg.`id_category` = c.`id_category`)
 WHERE `id_parent` = '.(int)($category_id).'
 '.($active ? 'AND `active` = 1' : '').'
 AND cg.`id_group` '.$sqlGroups.'
 GROUP BY c.`id_category`
 ORDER BY `level_depth` ASC, c.`position` ASC');
 foreach ($result AS &$row)
 {
  $row['id_image'] = (file_exists(_PS_CAT_IMG_DIR_.$row['id_category'].'.jpg')) ? (int)($row['id_category']) : Language::getIsoById($id_lang).'-default';
  $row['legend'] = 'no picture';
 }
 return $result;
}
public function getSubCategoriesRecursive($category_id){
 $subcategory_result = $this->getSubCategoriesOfCategoryID((int)($category_id));
 if(sizeof($subcategory_result)>0){
  foreach($subcategory_result as $subcat){
    $this->recursive_categories[] = $subcat['id_category'];
    $this->getSubCategoriesRecursive($subcat['id_category']);
  }
 }
 return false;
}

 

and in the same file, paste the following line just before __construct:

 

public $recursive_categories = array();

 

Then inside getTree, comment out the following:

 

$ProductsCount = (int)Db::getInstance()->getValue('SELECT COUNT(*) FROM '._DB_PREFIX_.'category_product WHERE id_category = '. $id_category);

 

and paste the following after it:

 

$this->recursive_categories[] = (int)($id_category);
$this->getSubCategoriesRecursive((int)($id_category));

$combined_categories = $this->recursive_categories;

$ProductsCount = (int)Db::getInstance()->getValue('
 SELECT COUNT(DISTINCT cp.`id_product`) AS total
 FROM `'._DB_PREFIX_.'product` p
 LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON p.`id_product` = cp.`id_product`
 WHERE cp.`id_category` IN ('.implode(',',$combined_categories).')'.($active ? ' AND p.`active` = 1' : ''));

[DylzEn]

Mr. Andrew R, you almost reached the status of awesome to me. I attached 3 screenshots from my store so you can see understand better what is wrong now. I have 7 products in my store, specifically 1 product in "World Banknotes --> Asia --> Abkhazia" and 6 products in "World Banknotes --> Asia --> Afghanistan". So, the numbers next to "World Banknotes" and "Asia" (7) are correct, and so is the number next to "Abkhazia" (1), but, as you can see, every other Asia subcategory shows (7) instead of (0) and the same happens for Europe and Oceania and every subcategory of those two. I've tried to figure out what to edit in your code but I didn't come up with a solution. You already helped me a lot so I can only thank you for all your help and hope you can point me in the right direction once again. Thank you, sir.

 

Dylan

 

[Andrew R]

I have 7 products in my store, specifically 1 product in "World Banknotes --> Asia --> Abkhazia" and 6 products in "World Banknotes --> Asia --> Afghanistan". So, the numbers next to "World Banknotes" and "Asia" (7) are correct, and so is the number next to "Abkhazia" (1), but, as you can see, every other Asia subcategory shows (7) instead of (0) and the same happens for Europe and Oceania and every subcategory of those two.

 

Try changing the following line in getTree:

 

$this->recursive_categories[] = (int)($id_category);

 

to this:

 

$this->recursive_categories = array((int)($this->id));

[DylzEn]

Thank you for the quick reply. Attached is what I'm getting with that change. Any thought? It looks like you're getting closer though.

 

[Andrew R]

Try changing the database query for $productsCount to:

 

$ProductsCount = (int)Db::getInstance()->getValue('
SELECT COUNT(DISTINCT cp.`id_product`) AS total
FROM `'._DB_PREFIX_.'product` p
LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON p.`id_product` = cp.`id_product`
WHERE cp.`id_category` IN ('.implode(',',$combined_categories).') AND p.`active` = 1');

[DylzEn]

I'm pretty sure nothing changed in the categories after this last change at the query. I'll attach my blockcategories.php if it can help you. Thanks again for your time.

 

blockcategories.php

Edited October 1, 2012 by DylzEn (see edit history)

[Andrew R]

I'm pretty sure nothing changed in the categories after this last change at the query. I'll attach my blockcategories.php if it can help you. Thanks again for your time.

 

I made a typo up above. The line in getTree that says:

 

$this->recursive_categories = array((int)($this->id));

 

should have been:

 

$this->recursive_categories = array((int)($id_category));

[DylzEn]

That is perfect! It works as it should, I've searched a solution for months and you finally resolved it for me. Thank you very much.

Only one last problem though: I was in development mode until now, and I had "Force compile" on YES and "Cache" on NO. The problem is that if I set "Cache" on YES (prestashop says it is recommended), the brackets and the numbers next to the categories completely disappear, looking as the default module. Any idea on why that happens? Do you suggest me to leave the "Cache" option disabled? Thank you again.

 

Dylan

[Andrew R]

I was in development mode until now, and I had "Force compile" on YES and "Cache" on NO. The problem is that if I set "Cache" on YES (prestashop says it is recommended), the brackets and the numbers next to the categories completely disappear, looking as the default module. Any idea on why that happens? Do you suggest me to leave the "Cache" option disabled?

 

The cache option should always be enabled but after you've made a change to your code, it has to recompiled to the cache so you would turn on force recompile, visit the page you changed to update the cache and then disable force recompile.

[DylzEn]

The cache option should always be enabled but after you've made a change to your code, it has to recompiled to the cache so you would turn on force recompile, visit the page you changed to update the cache and then disable force recompile.

Nothing, I tried every combination and refreshed everytime, but as long as the Cache option is ON, the numbers just don't show up next to categories. Have no idea what could be causing this...

 

[Andrew R]

Nothing, I tried every combination and refreshed everytime, but as long as the Cache option is ON, the numbers just don't show up next to categories. Have no idea what could be causing this...

 

Have you made sure your cache directory is writeable? It's in tools/smarty_v2/cache. You can empty both cache and compile folders, make sure they are writeable using chmod 777 in a terminal. It will generate the files again once you visit pages and they should be new versions of the files.

 

The cache isn't all that important really. Servers are fast enough these days to run through PHP code. The compilation step is the bigger slow down.

[DylzEn]

Have you made sure your cache directory is writeable? It's in tools/smarty_v2/cache. You can empty both cache and compile folders, make sure they are writeable using chmod 777 in a terminal. It will generate the files again once you visit pages and they should be new versions of the files.

I emptied "cache" and "compile" folders from "smarty", set 777 permissions on both the folders and now everything works even with "Cache" option enabled. You, sir, are awesome. I don't know how to thank you or return the favour, you really gave me a huge help. I'm going to add "Solved" in the title, hoping I won't have any other problems. Thank you again, Andrew R.

[ammarshadiq]

To Clarify, the files that you should edited are located at themes/my_theme/modules/blockcategorys/category-tree-branch.tpl

clear the cache, the compile folder, and refresh the page.

 

Hope this helps.