Move out of stock last if qty is 0

Question

I use an observer to display out of stock products last.
That works fine, but now I want to enable 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, if status is "Out of stock"?

Observer:

<?php

class Outofstock_Model_Observer extends Mage_Core_Model_Abstract
{
    public function catalogProductCollectionLoadBefore(Varien_Event_Observer $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');
        $order = $collection->getSelect()->getPart('order');
        array_unshift($order, array_pop($order));
        $collection->getSelect()->setPart('order', $order);


    }
}

Correct code is, thanks to @Giuseppe:

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');

    }
}

Answer 1

to give you a trail to follow, in a similar scenario, I've to show last the product with qty =0, I've written this code in a local override of: Catalog/Block/Product/List.php

        $this->_productCollection = $layer->getProductCollection();
        //Added the lines below...
        $this->_productCollection->joinField(
                    'inventory_qty',
                    '<table_prefix>cataloginventory_stock_item',
                    'qty',
                    'product_id=entity_id')
                ->setOrder('inventory_qty','desc');

the above will sort the collection on qty DESC, you can use as a start point to modify your code.

Hope this helps if not my code applied to your code should be like the following one

public function catalogProductCollectionLoadBefore(Varien_Event_Observer $observer)
{
    $collection = $observer->getCollection();
    $collection->joinField(
                        'inventory_qty',
                        'cataloginventory_stock_item',
                        'qty',
                        'product_id=entity_id')
                    ->setOrder('inventory_qty','desc');

}

Giuseppe