I want a list of category IDs, which have products that have...
- visibility: Catalog or Catalog and Search
- status: enabled (for the given store)
I can't find a built-in way to do this that is fast.
I can do this by getting product collection and filter it by
visibility and then combine the category IDs from all of the products, (
array_intersect() or the like,) but this is very slow.
I can do this by getting all of the categories and then checking their product counts, but this includes products which are disabled or hidden.
(As a side note: the database table
catalog_category_product_index_enbl_idx suggests that this is an index of enabled products, by category. But the table is empty, and no code seems to use it that I can find.)
The site I'm working on has about 50,000 products and about 2000 categories, so I need something very fast. Right now, the fastest way for me to get this is by using two queries:
Categories with visible products:
SELECT `category_id` FROM `catalog_category_product_index` WHERE `store_id` = 1 AND `visibility` IN (2,4) GROUP BY `category_id`
Categories with enabled products:
SELECT ccpi.`category_id` FROM catalog_product_entity_int cpei1 LEFT JOIN catalog_product_entity_int cpei2 ON cpei1.entity_id = cpei2.entity_id AND cpei2.`store_id` = 1 AND cpei2.`attribute_id` = 273 AND cpei2.`value` = 1 INNER JOIN catalog_category_product_index ccpi ON ccpi.`product_id` = cpei1.`entity_id` WHERE cpei1.`store_id` = 0 AND cpei1.`attribute_id` = 273 AND ( cpei2.`value` = 1 OR (cpei2.`value` IS NULL AND cpei1.`value` = 1) ) GROUP BY ccpi.`category_id`
These queries are built as future-proof as I can build them, but getting all of the table names and attribute IDs and values like this:
$cpei = $resource->getTableName('catalog_product_entity_int'); $ccpi = $resource->getTableName('catalog_category_product_index'); $storeId = (int)Mage::app()->getStore()->getStoreId(); $visibleValues = array( Mage_Catalog_Model_Product_Visibility::VISIBILITY_IN_CATALOG, Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH ); $enabledAttrId = Mage::getModel('eav/entity_attribute')->loadByCode('catalog_product', 'status')->getId(); $enabledValue = (int)Mage_Catalog_Model_Product_Status::STATUS_ENABLED;
However, it's still pretty messy. It's extremely fast, and gives me every category ID with enabled, visible products, but it's not the "right" way.