Best practice to fetch all configurable products + one "child" simple product

Question

I'm trying to improve some bad code that is causing a really slow load time.

The code is used for some carousel displaying products.

You can see it here:

<?php
// Get all configurable products for the carousel.
$_productCollection_configurablesOnly = Mage::getResourceModel('catalog/product_collection')
    ->addAttributeToSelect('*')
    ->addAttributeToFilter('type_id','configurable')
    ->addAttributeToFilter('status', array('neq' => 2));
//->addAttributeToFilter('sku', array('nin' => array("SM106","SM107","SM110","SM107","SM110","SM111","SM116","SM121","SM118","SM119")));
//->addAttributeToFilter('sku', array('in' => array("SMX102")));



foreach ($_productCollection_configurablesOnly as $conf_product) {

    if ($conf_product->get_status() != 2) :

        // Get all simple products.
        // $simple_products = $conf_product->getTypeInstance(true)->getUsedProducts(null, $conf_product);
        $simple_products = Mage::getModel('catalog/product_type_configurable')->getUsedProducts(null, $conf_product);

        // Remove disabled simple products from the list.
        foreach ($simple_products as $k => $simple_product) {
            $simple_product_loaded = Mage::getModel('catalog/product')->load($simple_product->getId());
            if ($simple_product_loaded->getStatus() == 2) {
                // Product is disabled.
                unset($simple_products[$k]);
            }
        }
        $simple_products = array_values($simple_products);

        // Choose a random one.
        $random_simple_product = $simple_products[rand(0, count($simple_products) - 1)];

        // Get main image from the random simple product and from configurable product.
        if ($random_simple_product->getImage() && $conf_product->getImage()) {
            $random_simple_product_main_image = (string)$this->helper('catalog/image')->init($random_simple_product, 'white_bg_image')->keepFrame(false)->resize(500);
            $conf_product_main_image = (string)$this->helper('catalog/image')->init($conf_product, 'image')->keepFrame(false)->resize(500);
        } else {
            $random_simple_product_main_image = "";
            $conf_product_main_image = "";
        }

        // Get color. Replace spaces and ampersands with dashes to support URLs.
        $color = str_replace('&', '-', str_replace(' ', '-', $random_simple_product->getAttributeText('color')));
        ?>

        <div class="item">
            <a href="<?php echo $conf_product->getProductUrl() . '#' . $color ?>">
                <img class="hover" src="<?php echo $random_simple_product_main_image; ?>" alt="<?php echo $conf_product->getName(); ?>">
                <img class="no-hover" src="<?php echo $conf_product_main_image; ?>" alt="<?php echo $conf_product->getName(); ?>">
            </a>
        </div>

    <?php endif; ?>
<?php } ?>

</div>
<?php //endif; ?>

I'm aware of the fact that it gets all the configurable products (with all their attrs) and then getting all the simple products assigned to those. and pick one random.

I'm looking for the right way to do it without over-killing and selecting unnessecary data.

Answer 1

This is a perfect example of getUsedProducts() method being used incorrectly. You should never use it within collection of product to retrieve a single attribute value. In your case it is product image.

The alternative approach is:

1. Set a custom flag to a product collection via setFlag() method
2. Create an observer for after load event of product collection catalog_product_collection_load_after, that will check a custom flag in order to trigger your custom image retrieval logic.

3. Create a custom resource model that will be used by event observer and does the following:

   • Takes all ids of configurable products from collection by using getColumnValues('entity_id') method
   • Does a query to catalog/product_super_link table to retrieve a single random in-stock simple product
   • Does a query for the simple product image attribute values (catalog/product_entity_varchar table for attribute code white_bg_image)
   • Calls setData('white_bg_image', $retrievedValue) on configurable product to dynamically set this image to configurable.

4. In your template then just use configurable product for image generation, instead of heavy simple product logic retrieval.