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: