Filter Category Collection by Enabled, Visible Products

Question

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:

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.

Answer 1

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):

Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($collection); 

Answer 2

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