1

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.

  • 1
    loading in a loop is a "no-no" :-) – user487772 Jul 4 '14 at 21:51
3

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;

    ?>
  • @Good works.. James – Amit Bera Jul 5 '14 at 4:31
  • Okay but this isn't what I want. 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. – Michel Gokan Jul 5 '14 at 7:01
  • See update in the question. – Michel Gokan Jul 5 '14 at 7:07
  • OK Miko, no problem I missed the break; in your code. I have added the group->('parent_id') clause so now it will only count once per parent. – James Anelay - TheExtensionLab Jul 5 '14 at 10:23
  • 1
    See updates Miko, within //Update part 1 start and //Update part 2 start comments – James Anelay - TheExtensionLab Jul 10 '14 at 21:44
1

Just for Reference, I used sample data of magento 1.9, and this code broke. On debugging the reason was count() function. Magento 1.9 keeps surprising me, getSize() worked for me.

<?php

      $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());
    //getSize worked for me, in magento 1.9 with sample data installed.
    echo $productCollection->getSize();

    //$cnt = $productCollection->getCount(); error displayed
    echo $cnt;

    ?>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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