Product Collection for Default Category

Question

I'm attempting to get a list of products that are only assigned to the default category and not assigned to any other categories.

I tried the following:

// default category id
$categoryId = 2;

$category = Mage::getModel('catalog/category')->load($categoryId);
$_products = $category->getProductCollection()->addAttributeToSelect('*');

echo $_products->getSize();

The product sized echoed back to me is 5. This is way smaller than the actual product count should be. Remember I'm looking for only products assigned to just the default category.

If I investigate the SQL query that is ran from the above collection I find something interesting (note I truncated the fields in the original dump from echoing $_products->getSelect())

SELECT 1 AS `status`,
       `e`.`sku`,
       `e`.`entity_id`,
       `e`.`type_id`,
       `cat_index`.`position` AS `cat_index_position`    
FROM `catalog_product_flat_1` 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.category_id = '2'
AND cat_index.is_parent=1

Running the SQL query returns the 5 rows of products. I noticed that none of these 5 products have a category assigned. They are not even assigned to a category how are they showing up when I query category_id 2 (default category).

If can verify that they dont have a category assigned by the following query:

SELECT cc.* FROM catalog_category_entity cc
JOIN catalog_category_product cp ON cc.entity_id = cp.category_id
WHERE cp.product_id IN (
    SELECT `e`.`entity_id`   
    FROM `catalog_product_flat_1` 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.category_id = '2'
    AND cat_index.is_parent=1
)

The query below is more or less what I want to achieve but using Magento collections:

SELECT cp.product_id, cc.* FROM catalog_category_entity cc
JOIN catalog_category_product cp ON cc.entity_id = cp.category_id
WHERE path = '1/2'

Does anybody have any ideas how to use the collection model to retrieve a list of products that are ONLY assigned to the Default Category (in my case category_id = 2)?

I could be overlooking something simple. Thanks.

Answer 1

Okay answering my own question here:

It seems the _applyProductLimitations() function of Mage_Catalog_Model_Resource_Product_Collection adds a WHERE condition automatically to the collection if the category is not an anchor.

public function addCategoryFilter(Mage_Catalog_Model_Category $category) {
        $this->_productLimitationFilters['category_id'] = $category->getId();
        if ($category->getIsAnchor()) {
            unset($this->_productLimitationFilters['category_is_anchor']);
        } else {
            $this->_productLimitationFilters['category_is_anchor'] = 1;
        }
...

If the category is not an anchor it attaches the condition here:

if (isset($filters['category_is_anchor'])) {
    $conditions[] = $this->getConnection()
        ->quoteInto('cat_index.is_parent=?', $filters['category_is_anchor']);
}

So to retrieve a list of products just in the "Default Category" or ROOT Category you can do the following.

$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();