Displaying Product Count and Price Ranges for subcategories in thirty bees

In this quick tutorial, you will learn how to add the product count, as well as the price ranges, for subcategories in ThirtyBees, to give your customer a better understanding of your catalog.

Step 1 – Retrieving the product count for each subcategory

The best approach when adding new functionalities to thirty bees is to use overrides. Let’s add a new one for the Category class, which includes the function that retrieves subcategories in thirty bees.

Create a new file named Category.php inside override/classes, and open it up in your favorite code editor. Then, let’s start by adding some simple override code, inside php tags:

class Category extends CategoryCore
{
}

We want to extend the method named getSubcategories, and even if it should be the same across the last thirty bees version, it’s always best to copy your own, from the original Category.php. Mine, from ThirtyBees 1.0.2, looks like this:

public function getSubCategories($id_lang, $active = true)
{
$sql_groups_where = '';
$sql_groups_join = '';
if (Group::isFeatureActive()) {
$sql_groups_join = 'LEFT JOIN `'._DB_PREFIX_.'category_group` cg ON (cg.`id_category` = c.`id_category`)';
$groups = FrontController::getCurrentCustomerGroups();
$sql_groups_where = 'AND cg.`id_group` '.(count($groups) ? 'IN ('.implode(',', $groups).')' : '='.(int)Group::getCurrent()->id);
}

$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
'.Shop::addSqlAssociation('category', 'c').'
LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (c.`id_category` = cl.`id_category` AND `id_lang` = '.(int)$id_lang.' '.Shop::addSqlRestrictionOnLang('cl').')
'.$sql_groups_join.'
WHERE `id_parent` = '.(int)$this->id.'
'.($active ? 'AND `active` = 1' : '').'
'.$sql_groups_where.'
GROUP BY c.`id_category`
ORDER BY `level_depth` ASC, category_shop.`position` ASC');

$formated_medium = ImageType::getFormatedName('medium');

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

Paste the whole method into our new override.
The next step is actually retrieving the product count itself. We are going to instantiate a new category object for each subcategory, so you might want to consider an alternative way in case you have many (more than 10).
In the last foreach loop, after the “no picture” part, add the following:

class Category extends CategoryCore
{
$category = new Category($row['id_category']);
$row['product_count'] = $category->getProducts($id_lang, 1, 0, null, null, true);
}

As simple as that. We first instantiate the category object, give then id we got out of the loop, and then use the builtin getProducts method, passing in true as sixth parameter, in order to get the total number of products for that category.
Please notice that instanciating several objects might be resource-intensive, a note of caution must be considered, again, if you have a large number of subcategories.

Retrieving the price span for a certain category in thirty bees

This part is optional, as it requires the Layered Navigation Module to be installed. If you are not using it, you can simply install, then disable it.
However, before proceeding, make sure you have your prices indexed in the module, by clicking the link shown in the next screenshot:

Now that we have everything set, it’s time to get to coding. The process is a little annoying, and what we can get with this method is just an approximation of the price, which is what is stored in the layered navigation basically. If you need a better range, like when you use psychological pricing, you might have to store minimum and maximum prices yourself, using an alternative way.
First, we retrieve a list of the product IDs for the current subcategory, and if we have any, we clean up the result array, so that each value is a simple number, and not another array:

if(!$row['product_count'])
continue; // if no products, skip to the next subcategory
$product_ids = $this->getProductsWs();
// this would return products as array, with id => # as structure
// and therefore needs a cleanup
if($product_ids)
{
foreach ($product_ids as $product) {
$product_ids_clean[] = $product['id'];
}
}

Lastly, we can simply add the following query to our previous code, just after the one we used to get the product count:

$row['price_span'] = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow('
SELECT MIN(price_min) as min_price, MAX(price_max) as max_price
FROM '._DB_PREFIX_.'layered_price_index
WHERE price_min > 0 AND id_product IN ('.implode(',', $product_ids_clean).')
');

We are using this query to retrieve the minimum and maximum prices of the shop’s subcategory we are currently viewing, making sure the minimum price is not zero.

Before proceeding at this point, since we used an override, it’s necessary to erase the class_index.php file, located in the cache/ folder, so that our override can be loaded and seen by thirty bees.

Displaying the product count and price range for subcategories, in the thirty bees default template

The last thing we have to do is add the stuff we just retrieved to the template.
Let’s open up category.tpl, located in the theme folder. The structure will vary a lot depending on your own theme, as for this example I will use the community-default-theme that comes with ThirtyBees 1.0.2.

Locate the subcategories loop in the template:

{foreach from=$subcategories item=subcategory}

And make sure you stay inside it with any code we will write now. Where to add the snippet is entirely up to you, I chose the very end of the block, right before the closing LI element.

{foreach from=$subcategories item=subcategory}

...

{$subcategory.product_count} {l s='Products'}

{if $subcategory.product_count}

{displayPrice price=$subcategory.price_span.min_price} - {displayPrice price=$subcategory.price_span.max_price}

{/if}

{/foreach}

And that’s basically it!
We are done, here is how it should display in the community-default-theme theme of thirty bees 1.0.2.

p.s. You will notice it’s reading 1 Products. I didn’t add any extra code to keep it slim, but you might if you run a small shop with products that only have one product.