Collection of products in all child categories

A task I had to accomplish was to itterate all categories, and disable the categories which had no products enabled, or had no products assigned to them (filtering down the category tree, thus an anchor category, that has children with no products was also to be set disabled, filter all the way up the tree.

The end result on a category tree would be:

Easy stuff, and no problem doing that, but I found an issue when trying to go the other way.

The scenario is that after a product import from the master products database, the child category will now have products, thus I need to reverse the disabling routine to enable the category tree.

This *should* have been a simple matter of taking each category, and request a product collection, and if there are products in the result, then enable it. AN anchor category *should* return the child category products, thus can be set enabled.

As usual, life is not that simple. The child enabled fine, but once I tried the parent, even though it is an anchor, returned no products. Re-indexing did not fix the issue.

Here is the code that should have worked:

$productCollection = $category->getProductCollection()
                    ->addCategoryFilter($category)
                    ->addAttributeToFilter('status', Mage_Catalog_Model_Product_Status::STATUS_ENABLED)
                    ->addAttributeToFilter('type_id', 'configurable')
                    ->load();

$category is the current category I am itterating over. Now here I must mention that if I use the same code on the root category, which is set as anchor as well, then it works, so I must be missing something, or it IS an indexing issue.....

The corresponding sql looks like this:

SELECT `e`.*, `cat_pro`.`position` AS `cat_index_position`, `at_status`.`value` AS `status` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_category_product` AS `cat_pro` ON cat_pro.product_id=e.entity_id AND cat_pro.category_id='1983'
 INNER JOIN `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = '89') 
AND (`at_status`.`store_id` = 0) WHERE (at_status.value = '1') AND (`e`.`type_id` = 'configurable')

To get around the issue, I abaondoned the

->getProductCollection()

method, and used the product collection direct, as such:

$productCollection = Mage::getResourceModel('catalog/product_collection')
                        ->setStoreId(0)
                        ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id=entity_id', null, 'left')
                        ->addAttributeToFilter('category_id', array('in' => $categoryLimit))
                        //->addAttributeToSelect('*')
                        ->addAttributeToFilter('status', Mage_Catalog_Model_Product_Status::STATUS_ENABLED)
                        ->addAttributeToFilter('type_id', 'configurable');
                $productCollection->getSelect()->group('product_id')->distinct(true);
                $productCollection->load();

Here I join the category table, and I pass it an array using

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

which holds the id's of the categories I am interested in.

The $categoryLimit is build before using:

if ($category->hasChildren()) {
                    $categoryLimit = explode(',',$category->getAllChildren());
                } else {
                    $categoryLimit = $category->getId();
                }

an important part here is the

$productCollection->getSelect()->group('product_id')->distinct(true);

which adjusts the resulting sql to remove any duplicate records on the product_id, elese you will get an exception thrown as the resulting collection will want to load duplicate item id's into it's data array. This happens if you have the same product attached to a parent category, and one of its child categories.

The corresponding sql is:

SELECT DISTINCT `e`.*, `at_category_id`.`category_id`, `at_status`.`value` AS `status` FROM `catalog_product_entity` AS `e`
 LEFT JOIN `catalog_category_product` AS `at_category_id` ON (at_category_id.`product_id`=e.entity_id)
 INNER JOIN `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = '89') AND (`at_status`.`store_id` = 0) WHERE (at_category_id.category_id IN('1983')) AND (at_status.value = '1') AND (`e`.`type_id` = 'configurable') GROUP BY `product_id`

Now I can effectively check if a parent category has any active configurables, and ebale it, and not care about the isAnchor attributes setting.