Change Category Tree

[pavila]

Hello everyone,

 

My first post in the forum.

 

I need to change the categories shown on the categories tree. Right now, when a category on click from the top menu all the top categories and next depth subcategories are shown as a tree.

 

Instead of a tree, what I need is to have listed all the subcategories which have products associated.

How can I achieve this? I already have the SQL necessary to retrieve all the subcategories with products from a certain top category, it will looks something like this:

 

SELECT *

FROM ps_category 

INNER JOIN (

SELECT ps_category.nleft, ps_category.nright

FROM ps_category INNER JOIN (

SELECT nleft, nright 

FROM ps_category 

WHERE ps_category.id_category = $id_category

   ) sel_category on (ps_category.nleft<=sel_category.nleft AND ps_category.nright>=sel_category.nright)

WHERE ps_category.id_parent = (SELECT id_category FROM ps_category WHERE ps_category.id_parent = 1)

  ) top_category on (ps_category.nleft>top_category.nleft and ps_category.nright<top_category.nright)

WHERE EXISTS (SELECT 1 FROM ps_category_product WHERE ps_category_product.id_category = ps_category.id_category LIMIT 0,1)

 

But where to I have to apply this SQL statement, following the best pratices, etc?

 

Thanks.

 

Kind regards.

Pedro Avila

[vekia]

block categories with counter + modification:

 

open category-tree-branch2.tpl and add if condition to check how many products category contains

{if $node.productsCount > 0}

{/if}

, like:

{if $node.productsCount > 0}
<li class="blockcategoryitem_{$node.id} {if isset($last) && $last == 'true'}last{/if}">
	<a href="{$node.link|escape:'htmlall':'UTF-8'}" {if isset($currentCategoryId) && $node.id == $currentCategoryId}class="selected"{/if}
		title="{$node.desc|strip_tags|trim|escape:'htmlall':'UTF-8'}">{$node.name|escape:'htmlall':'UTF-8'} ({$node.productsCount})</a>
	{if $node.children|@count > 0}
		<ul>
		{foreach from=$node.children item=child name=categoryTreeBranch}
			{if $smarty.foreach.categoryTreeBranch.last}
				{include file="$branche_tpl_path" node=$child last='true'}
			{else}
				{include file="$branche_tpl_path" node=$child last='false'}
			{/if}
		{/foreach}
		</ul>
	{/if}
</li>
{/if}

[pavila]

Hi Vekia,

 

Thank you for your answer. 

 

I installed the module you have mentioned. The products total per category is great, however it is still presenting top categories from other then the one I have clicked.

 

I'm trying to list all subcategories of a top menu category, which have products associated. The blockcategories2 module shows every categories, even all top categories, with products count, and with the suggested change, no categories without products will be shown.

 

After reading about modules creation, and not having the chance of overriding modules, yesterday I started to build my first module, with the hope of finding an answer to my quest. Maybe it will turn on my first contribution to the community.

 

Kind regards.

Pedro Ávila

[tdc666]

if you figure this out, please post up your results. i have been looking for a solution to this for some time now. 

[pavila]

Hey guys,

 

I managed to dev the module with the caracteristics that i wanted. I will release it, as soon as I have it fully tested and working as I want it to be.

 

Thank you for your help.

 

Kind regards

Pedro Ávila