Magento – Configurable Product turn Out of Stock when all child product are out of stock

Here is the solution that worked for me:

the problem is with the code used in Simple Configurable Products (OrganicInternet)

I would like to note that I was having two seperate issues at once - the above, and the product price index refused to be indexed (I was recieving this message: Cannot intialize the indexer process, which was explained as SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1)

as it turned out both problems were related, and below solved them both ;)

1. in the file / app / code / community / OrganicInternet / SimpleConfigurableProducts / Catalog / Model / Resource / Eav / Mysql4 / Product / Indexer / Price / Configurable.php

change:

$select->columns(array(
        'entity_id'         => new Zend_Db_Expr('e.entity_id'),
        'customer_group_id' => new Zend_Db_Expr('pi.customer_group_id'),
        'website_id'        => new Zend_Db_Expr('cw.website_id'),
        'tax_class_id'      => new Zend_Db_Expr('pi.tax_class_id'),
        'orig_price'        => new Zend_Db_Expr('pi.price'),
        'price'             => new Zend_Db_Expr('pi.final_price'),
        'min_price'         => new Zend_Db_Expr('pi.final_price'),
        'max_price'         => new Zend_Db_Expr('pi.final_price'),
        'tier_price'        => new Zend_Db_Expr('pi.tier_price'),
        'base_tier'         => new Zend_Db_Expr('pi.tier_price'),
    ));

to:

$select->columns(array(
        'entity_id' => new Zend_Db_Expr('e.entity_id'),
        'customer_group_id' => new Zend_Db_Expr('pi.customer_group_id'),
        'website_id' => new Zend_Db_Expr('cw.website_id'),
        'tax_class_id' => new Zend_Db_Expr('pi.tax_class_id'),
        'orig_price' => new Zend_Db_Expr('pi.price'),
        'price' => new Zend_Db_Expr('pi.final_price'),
        'min_price' => new Zend_Db_Expr('pi.final_price'),
        'max_price' => new Zend_Db_Expr('pi.final_price'),
        'tier_price' => new Zend_Db_Expr('pi.tier_price'),
        'base_tier' => new Zend_Db_Expr('pi.tier_price'),
        'group_price' => new Zend_Db_Expr('pi.group_price'),
        'base_group_price' => new Zend_Db_Expr('pi.group_price'),
    ));

and

$outerSelect->columns(array(
        'customer_group_id',
        'website_id',
        'tax_class_id',
        'orig_price',
        'price',
        'min_price',
        'max_price'     => new Zend_Db_Expr('MAX(inner.max_price)'),
        'tier_price',
        'base_tier',
        #'child_entity_id'
    ));

to

$outerSelect->columns(array(
        'customer_group_id',
        'website_id',
        'tax_class_id',
        'orig_price',
        'price',
        'min_price',
        'max_price'     => new Zend_Db_Expr('MAX(inner.max_price)'),
        'tier_price',
        'base_tier',
    'group_price',
    'base_group_price',
        #'child_entity_id'
    ));

source: main issue together with this (however please note that the correct code is 'base_group_price' => new Zend_Db_Expr('pi.group_price'), and not 'base_group_price' => new Zend_Db_Expr('pi.base_group_price'),

2. also, in the file: / app / code / community / OrganicInternet / SimpleConfigurableProducts / Catalog / Model / Resource / Eav / Mysql4 / Product / Indexer / Price.php

change

$this->cloneIndexTable(true);

to

$this->clearTemporaryIndexTable();

source: here

It took me several hours to figure this out so I wrote this post to help anyone else from wasting all that time.

best of luck!

Great find Alex! A tip: If you want to avoid changing core files (and possibly make this into a module), you can add it to an event listener, like this (tested on 1.8.1.0):

/**
 * Fires before a product collection is loaded
 *
 * @param Varien_Event_Observer $observer
 */
public function catalog_product_collection_load_before($observer)
{
    $collection = $observer->getCollection();
    $collection->getSelect()->joinLeft(
        array('_inventory_table'=>$collection->getTable('cataloginventory/stock_item')),
        "_inventory_table.product_id = e.entity_id",
        array('is_in_stock', 'manage_stock')
    );
    $collection->addExpressionAttributeToSelect(
        'on_top',
        '(CASE WHEN (((_inventory_table.use_config_manage_stock = 1) AND (_inventory_table.is_in_stock = 1)) OR  ((_inventory_table.use_config_manage_stock = 0) AND (1 - _inventory_table.manage_stock + _inventory_table.is_in_stock >= 1))) THEN 1 ELSE 0 END)',
        array()
    );
    $collection->getSelect()->order('on_top DESC');
    // Make sure on_top is the first order directive
    $order = $collection->getSelect()->getPart('order');
    array_unshift($order, array_pop($order));
    $collection->getSelect()->setPart('order', $order);
}

Edit: Changed back to catalog_product_collection_load_before from catalog_product_collection_apply_limitations_before and fixed order part priority.