Magento – Move out of stock product in category view last if qty is 0

event-observermagento-1.9manage-stockout-of-stockstock

I use a module to display out of stock products last in the category view.
That worked fine, but now I enabled the Magento option to purchase products that are out of stock. (The default Magento backorder method)

In that case I need to set all products to status "In Stock", so this code does not work anymore.

How can I edit this code, so that it will check if qty is 0, instead of status is "Out of stock"?

Default code, that works fine when set product to "Out of Stock" status:

class Outofstock_Model_Observer extends Mage_Core_Model_Abstract
{
    public function catalogProductCollectionLoadBefore(Varien_Event_Observer $observer)
    {
        $prefix = '_inventory_table'.rand(0, 10000);
        $collection = $observer->getCollection();
        $collection->getSelect()->joinLeft(
            array($prefix=>$collection->getTable('cataloginventory/stock_item')),
            "$prefix.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('(CASE WHEN ((('.$prefix.'.use_config_manage_stock = 1) AND ('.$prefix.'.is_in_stock = 1)) OR  (('.$prefix.'.use_config_manage_stock = 0) AND (1 - '.$prefix.'.manage_stock + '.$prefix.'.is_in_stock >= 1))) THEN 1 ELSE 0 END) DESC');
        $order = $collection->getSelect()->getPart('order');
        array_unshift($order, array_pop($order));
        $collection->getSelect()->setPart('order', $order);


    }
}

I already tried the code below, but that does not work perfectly. It does work, except that it does not overrule the catagory sort listing. So the sort listing is first displayed and then all products with the same sort listing are separated from stock and out of stock. I need to edit the code, so that it overrule the sort listing. So first list on stock and than on the sort listing number.
How can I fix this?

class Outofstock_Model_Observer extends Mage_Core_Model_Abstract
{
    public function catalogProductCollectionLoadBefore(Varien_Event_Observer $observer)
    {
        $prefix = '_inventory_table'.rand(0, 10000);
        $collection = $observer->getCollection();
        $collection->getSelect()->joinLeft(
            array($prefix=>$collection->getTable('cataloginventory/stock_item')),
            "$prefix.product_id = e.entity_id",
            array('is_in_stock', 'manage_stock')
        );
        $collection->joinField(
                        'inventory_qty',
                        'cataloginventory_stock_item',
                        'qty',
                        'product_id=entity_id')
                    ->setOrder('inventory_qty','desc');

    }
}

Best Answer

Not sure if this works, I haven't tested, but I used a similar approach to sort by custom attributes. So watch out for syntax errors.

You can rewrite the method Mage_Catalog_Model_Resource_Product_Collection::addAttributeToSort() and make it look like this:

public function addAttributeToSort($attribute, $dir = self::SORT_ORDER_ASC)
{
    //don't screw up the admin sorting.
    if (Mage::app()->getStore()->getId() == Mage_Core_Model_App::ADMIN_STORE_ID) {
        return parent::addAttributeToSort($attribute, $dir);
    }
    $website = Mage::app()->getWebsite();
    $select = $this->getSelect();
    //check if the correlation was already added
    $from = $select->getPart(Zend_Db_Select::FROM);
    //join the current collection with the stock status index table
    if (!isset($from['stock_status'])) {
        /** @var Mage_CatalogInventory_Model_Stock_Status $stockStatusModel */
        $stockStatusModel = Mage::getModel('cataloginventory/stock_status');
        $stockStatusModel->addStockStatusToSelect($select, $website);
    }
    //show first products that are in stock
    $select->order('stock_status.stock_status DESC');
    return parent::addAttributeToSort($attribute, $dir);
}
Related Topic