Hide empty categories in Magento

Question

I successfully hiden categories without any products from the navigation bar by following this helpful source http://prattski.com/2011/10/06/magento-module-hide-empty-categories/ and made a minor tweak inside Observer.php (as the solution of Prattski doesn't cover anchored categories and for my application I use image selection/presence to keep track of visibility of a product).

Anyways my question is this is slow: how do I optimize it?

=== STATISTICS ===

• Page load time without cache without category filter = 3 secs

• Page load time without cache with category filter = 9 secs

• Page load time with cache with category filter = 6 secs

=== CODE CHANGE ===

public function _removeHiddenCollectionItems($collection)
{
    // Loop through each category or product
    foreach ($collection as $key => $item)
    {
        // If it is a category
        if ($item->getEntityTypeId() == 3) {

            $category = Mage::getModel('catalog/category')->load($item->getId());
            $products = Mage::getResourceModel('catalog/product_collection')
            ->addCategoryFilter($category)
            ->addAttributeToFilter('image', array('neq' => 'no_selection'));

            $prodcount = $products->count();

            /*if ($item->getProductCount() < 1) {
                $collection->removeItemByKey($key);
            }*/

            if ($prodcount < 1){
                $collection->removeItemByKey($key);
            }
        }
    }
}

This code will work for anyone looking to remove the "No product in your selection" issue (only if you are not dealing with 25k+ products)... Hopefully someone out there could perfect it by optimizing the code to work faster!

Thanks

Answer 1

One of many tipps: You want to use getSize() instead of count().

Count gets the whole collection

public function getSize()
{
    if (is_null($this->_totalRecords)) {
        $sql = $this->getSelectCountSql();
        $this->_totalRecords = $this->getConnection()->fetchOne($sql, $this->_bindParams);
    }
    return intval($this->_totalRecords);
}

getSize() only gets the count if the collection is not loaded yet

public function count()
{
    $this->load();
    return count($this->_items);
}

Answer 2

This is a common problem which happens because of using import scripts.

As I prefer solutions, which affect as less surroundings as possible, I have solved this with an additional script.

This is a single script, which iterates over all categories, and deactivates them, if no visible products.
The performance impact on page loading times is zero.
The needed knowledge of Magento internals very low.

Answer 3

I don't think you should address this problem during the pageload. A better way, but perhaps a bit more advanced is to get this data in the flat category index table. (I assume you use flat catalog etc. Otherwise, you should!) or create a custom indexer for it. Though the first option would have my preference.

With the data indexed and making it part of default collection data, you can do, for example, $category->getProductCount() without having to do any additional queries. In your current solution you do a query to the DB for each and every category. In time, when your catalog grows this will drain even more of your performance.

There are many sources to read about creating indexers, underneath is one for reference: https://github.com/dbashyal/Magento-custom-indexer

Answer 4

all that sounds good, but what we face if we have

1. multistores when same product is visible in store#1 and invisible in store#2
2. simple products (visibility, quantity)
3. configurable products (visibility, also associated products visibility and quantity)
4. any of subcategories may have product so we cannot hide parent to be able navigate to that product

this conditions lead as to the heavy collections checks on page load, and in this circumstances the cron-single-script-to-deactivate-categories looking much better, any more ideas?