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.