Toolbar.php collection filtering using getCollection and addCategoryFilter not working

Question

I am using Magento 1.9.3.6 CE

I want to add a select dropdown on the toolbar of product lists on category pages to filter the collection by a subcategory. (sidenote: I can't use the normal catalog navigation to filter for other reasons).

I extended Mage_Catalog_Product_List_Toolbar and created a function for getting the count of child categories.

class Namespace_Catalog_Block_Product_List_Toolbar extends Mage_Catalog_Block_Product_List_Toolbar
{
    public function getCategoryCount($childCategory) {
        $count = $this->getCollection()->addCategoryFilter($childCategory)->getSize();
        return $count;
    }
}

But when I call this function in the block, it returns the count of the current category, not filtered by the child category.

The child category is loaded by $childCategory = Mage::getModel('catalog/category')->load($child->getId());.

Here is the SQL of $this->getCollection()->getSelect();

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price` FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '5' AND cat_index.is_parent=1 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 ORDER BY `cat_index`.`position` ASC LIMIT 16

Difference to below is here:

...
INNER JOIN `catalog_category_product_index` AS `cat_index`
ON cat_index.product_id=e.entity_id
AND cat_index.store_id=1
AND cat_index.visibility IN(2, 4)
AND cat_index.category_id = '5'
AND cat_index.is_parent=1
...

Here is the SQL of $this->getCollection()->addCategoryFilter($childCategory)->getSelect();

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price` FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '61' INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 ORDER BY `cat_index`.`position` ASC LIMIT 16

Difference to above is here:

...
INNER JOIN `catalog_category_product_index` AS `cat_index` 
ON cat_index.product_id=e.entity_id
AND cat_index.store_id=1 
AND cat_index.visibility IN(2, 4)
AND cat_index.category_id = '61'
...

The SQL is different, so why is it giving me the same count regardless...? (There are definitely not the same count of products in any of the subcategories).

Thanks in advance.

Answer 1

When we look at the getSize method from Varien_Data_Collection_Db:

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

It caches the total records.

To get the count from the updated collection after it first loads. See the version below:

class Namespace_Catalog_Block_Product_List_Toolbar extends Mage_Catalog_Block_Product_List_Toolbar
{
    public function getCategoryCount($childCategory) {
        $collection = $this->getCollection();
        $collection->addCategoryFilter($childCategory);;
        $count = $collection->getConnection()->fetchOne($collection->getSelectCountSql());
        return intval($count);
    }
}

There might be another solution, but that one should work.

Answer 2

The answer I have accepted by @Ner is I believe the correct answer to the question and will help the majority of users browsing this question.

I had a bit of a special scenario in that some of my queries used HAVING() statements ($collection->getSelect()->having()) and also a ->group() statement. This apparently causes an issue with the getSelectCountSql() function used in @Ner 's answer...

Further reading on the having()/group() issue:

Magento join query with group by and having error

https://stackoverflow.com/questions/3485455/using-group-breaks-getselectcountsql-in-magento

So my solution in my case was to extract the SQL and do a direct DB query to get the count:

class Namespace_Catalog_Block_Product_List_Toolbar extends Mage_Catalog_Block_Product_List_Toolbar
{
    public function getCategoryCount($childCategory) {
        $sql = $this->getCollection()->addCategoryFilter($childCategory)->getSelect();
        $results = Mage::getSingleton('core/resource')->getConnection('core_read')->fetchCol($sql);
        return count($results);
    }
}

As I say, very much an edge case so I think the accepted answer will help most people, I tested it without the having() and group() statements and it seemed to work.