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

I had a similar question and ended up answering my question here: https://magento.stackexchange.com/questions/24436/product-collection-for-default-category

$store_id = 1;
$root_category_id = Mage::app()->getStore(Mage::app()->getStore()->getId())->getRootCategoryId();

$collection = Mage::getResourceModel('catalog/product_collection')->setStoreId($store_id);
$model = Mage::getModel('catalog/product')->setStoreId($store_id);
$category_model = Mage::getModel('catalog/category');
$category = $category_model->load($root_category_id);
$category->setIsAnchor(true);
$collection->addCategoryFilter($category);

echo $collection->getSize(); // example to show you a count of products

Answer 2

scenario:

• Magento Version 1.7.0.2
• just one Store
• edited categories in the global mode
• deleted old root-category. So old products had no categories.
• Table "catalog_category_product_index" remains in a bad condition (even after reindexing) and shows to new root-category!

I build a helper-method:

    public function getCategoriesId($parent =  -1,$recursionLevel = -1, $asCollection = true)
{
    if($parent === -1){
        $categories = Mage::helper('catalog/category')->getStoreCategories(false, true);
    }else{
        $recursionLevel  = ($recursionLevel<0) ? max(0, (int) Mage::app()->getStore()->getConfig('catalog/navigation/max_depth')) : $recursionLevel;
        $categories = $category->getCategories($parent, $recursionLevel, false, $asCollection, true);
    }

    $ids = array();

    foreach($categories as $category){
        if($category->getId()){
            $ids[] = $category->getId();
        }
    }

    return $ids;
}

I used the result to filter the categories like jazzhand written:

    ->addAttributeToFilter('category_id', array('in' => $_rootcatID))

I'm not really sure, if its not just needed for the root-category.

Answer 3

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/>"; 
};

Answer 4

It didn't try it out, but I have two ideas:

(1) You can try to add an additional store filter to the category:

$collection = Mage::getModel('catalog/category')
     ->getCollection()
     ->addFieldToFilter('store_id', Mage::app()->getStore()->getId())
     ->addIdFilter(35); 

foreach ($collection as $item) {
     ...
}

Which is not very clean because the category_id is hardcoded.

(2) You could also try addPathFilter() on the collection (i don't know the root category path by heart, maybe simply "/" - but you can look it up in the database or use getPath() on the loaded root category). Maybe you have to use a store filter, too.

Answer 5

Try this:

$_testproductCollection = Mage::getResourceModel('catalog/product_collection') ->setVisibility(array(self::VISIBILITY_IN_CATALOG, self::VISIBILITY_BOTH)) ->addCategoryFilter($_category) ->addAttributeToSelect('*');