I want a list of category IDs, which have products that have...

  1. visibility: Catalog or Catalog and Search
  2. 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 status and 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:

    `store_id` = 1
    AND `visibility` IN (2,4)
GROUP BY `category_id`

Categories with enabled products:

    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`
    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(
$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.

2 Answers 2


If you are using flat tables, the flat table for each store (catalog_product_flat_x) will have only products that are Enabled, so loading a collection on a store scope in Magento should only give you enabled products.

As for the visibility, the Catalog Layer model uses the following method (I haven't tried it myself, but it should work):

$collection = Mage::getModel('catalog/product')->getCollection()->addAttributeToSelect('*');
$collection->addFieldToFilter('visibility', Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH)
->addAttributeToFilter('status', array('eq' => Mage_Catalog_Model_Product_Status::STATUS_ENABLED));
  • Welcome on Magento SE. One-liner and code-only answers are discouraged. Please explain the reasoning behind your answer. Commented Dec 8, 2016 at 16:14
  • This only gets the products, and not their categories IDs as requested, as is also very slow with a large catalog. (50K SKUs.)
    – Tyler V.
    Commented Dec 13, 2016 at 6:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.