Magento Configurable Products – Count In-Stock Associated Products

configurable-productperformanceproductproduct-collection

I used following code to retrieve number of in-stock associated products in a category ($cat) for a huge store with thousands of products.

$cnt = 0;
$prods = $cat->getProductCollection()
             ->addAttributeToSelect('entity_id')
             ->addAttributeToFilter('status',Mage_Catalog_Model_Product_Status::STATUS_ENABLED)
             ->addAttributeToFilter('visibility', array(Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH,Mage_Catalog_Model_Product_Visibility::VISIBILITY_IN_CATALOG))
             ->addAttributeToFilter('type_id',  'configurable');

foreach( $prods as $product) {
             $simpleProducts = Mage::getModel('catalog/product_type_configurable')
                                                  ->getUsedProducts(null,$product);

            foreach( $simpleProducts as $simple ){
                   $cvc = Mage::getModel('cataloginventory/stock_item')->loadByProduct($simple);

                   if($cvc->getIsInStock() && $cvc->getQty() > 0) {
                          $cnt++;
                          break;
                   }
           }
}

Above code works but its really slow. I mean it takes almost half an hour to calculate number of products for all categories in my store. What's the problem ? I know I can use caching (and I already did) but the question is that how can I make the code faster or what I missed in the above code that made it really slow ?

UPDATE

I want to count all configurable products with at least one simple product in stock. I don't care about configurable product stock by it self, only its associated products matter because in my store configurable product's in-stock availability don't matter. Only its associated products is important. I.e: if a configurable have 3 in-stock products, we should count it once.

Best Answer

Like Tim said in him comment above ideally you don't want to be running load within a loop as this causes extra work and MySQL calls. (Making it "really slow")

I had a play around and I think I have a rough version of what your trying to achieve - get a count of configurable products who have at least 1 associated products that are in stock in category X?

May not be perfect, but should work and much faster too.

    <?php
//Update part 1 start
$eavAttribute = new Mage_Eav_Model_Mysql4_Entity_Attribute();
$code = $eavAttribute->getIdByCode('catalog_product', 'status');
//Update part 1 end

    $currentCat = Mage::registry('current_category');

    //Gets all simple products that belong to a configurable product
    $productCollection = Mage::getResourceModel('catalog/product_type_configurable_product_collection')
        ->setFlag('require_stock_items', true)
        ->setFlag('product_children', true);

    //Adds stock, status and visibility filters.
    Mage::getSingleton('cataloginventory/stock')->addInStockFilterToCollection($productCollection);
    $productCollection->addAttributeToFilter('status', 1);
    $productCollection->addAttributeToFilter('visibility', 1);

    //Filters by simple products who's parents are in the current category
    $productCollection->getSelect()->joinLeft(
        array('ccp' => 'catalog_category_product'),
        'link_table.parent_id=ccp.product_id',
        array('*'))->where('ccp.category_id = ?',$currentCat->getId())->group('parent_id');

//Update part 2 start
    $productCollection->getSelect()->joinLeft(
        array('statust' => 'catalog_product_entity_int'),
        'link_table.parent_id=statust.entity_id',
            array('value'))->where('statust.value = ?',Mage_Catalog_Model_Product_Status::STATUS_ENABLED)->where('statust.attribute_id = ?',$code);

//Update part 2 end

    $cnt = $productCollection->count();
echo $cnt;

    ?>
Related Topic