Magento ->addCategoryFilter - filtering product collection by root category

Question

Concerning the use of the ->addCategoryFilter on a product collection. Why is it not possible to filter by the root category? I have 2 stores, both with different root categories. I want to show a list of best-selling products on my homepage. But I can't filter the products by the root category, only sub-categories under that.

So, on my homepage, all products from both stores show up. I can filter ok by any sub-categories. For example, my second root category has an ID of 35. If I try to filter by this, I get every product from both roots. But the first sub-category under that root is ID 36, and filtering by this works correctly, showing only those products. My call is as follows (simplified):

$_category = Mage::getModel('catalog/category')->load(35);

$_testproductCollection = Mage::getResourceModel('catalog/product_collection')
->addCategoryFilter($_category)
->addAttributeToSelect('*');
$_testproductCollection->load();

foreach($_testproductCollection as $_testproduct){ 
echo $this->htmlEscape($_testproduct->getName())."<br/>"; 
};

Anyone know why this isn't working? Or is there some other way to filter by the root category?

UPDATE: I still haven't had any luck with this. Seems very buggy - adding a category filter using the root category only works sometimes, you need to add all products to the root, then save, then remove any that shouldn't be in that root cat, then re-save. But if you re-index you get all products showing again. If I output the sql query from my above collection call I get the following:

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`, 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='2' AND cat_index.category_id='35' 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

As you can see, the category is listed there, so why doesn't the filter work?

Answer 1

OK, I think this works, haven't tested too much but seems to have done the trick. You need to first get your stores root category id, then join some fields so you have access to the products "category_id", then filter using that:

$_rootcatID = Mage::app()->getStore()->getRootCategoryId();

$_testproductCollection = Mage::getResourceModel('catalog/product_collection')
->joinField('category_id','catalog/category_product','category_id','product_id=entity_id',null,'left')
->addAttributeToFilter('category_id', array('in' => $_rootcatID))
->addAttributeToSelect('*');
$_testproductCollection->load();

foreach($_testproductCollection as $_testproduct){ 
    echo $this->htmlEscape($_testproduct->getName())."<br/>"; 
};