Out of Stock Products Order Module
Hi,
[Magento version 1.4.1.1]
We needed to sort the out-of-stock products on our products listing last. There was already a working solution here http://www.magentocommerce.com/boards/viewreply/213176/, but friends don’t let friends edit Magento core module files, so we created a quick module to encapsulate it.
We’ll need to create three files to make this work:
The Class File:
/app/code/local/CustomMage/Catalog/Model/Resource/Eav/Mysql4/Peoduct
We’ll be extending here from Magento core class Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection since we only need to add the following three lines:
$this->getSelect()->joinLeft( array('_inventory_table' => $this->getTable('cataloginventory/stock_item')), "_inventory_table.product_id = e.entity_id", array('is_in_stock', 'manage_stock') );
$this->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());
$this->getSelect()->order('on_top DESC');
So our new class file looks like this:
<?php
class CustomMage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection
extends Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection {
/**
* Add attribute to sort order
*
* Same as parent method, with the addition of left join
*/
public function addAttributeToSort($attribute, $dir='asc')
{
$this->getSelect()->joinLeft( array('_inventory_table' => $this->getTable('cataloginventory/stock_item')), "_inventory_table.product_id = e.entity_id", array('is_in_stock', 'manage_stock') );
$this->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());
$this->getSelect()->order('on_top DESC');
if ($attribute == 'position') {
if (isset($this->_joinFields[$attribute])) {
$this->getSelect()->order("{$attribute} {$dir}");
return $this;
}
$this->getSelect()->order("cat_index_position {$dir}");
// optimize if using cat index
$filters = $this->_productLimitationFilters;
if (isset($filters['category_id']) || isset($filters['visibility'])) {
$this->getSelect()->order('cat_index.product_id ' . $dir);
}
else {
$this->getSelect()->order('e.entity_id ' . $dir);
}
return $this;
}
$storeId = Mage::app()->getStore()->getId();
if ($attribute == 'price' && $storeId != 0) {
$this->addPriceData();
$this->getSelect()->order("price_index.min_price {$dir}");
return $this;
}
if($attribute == 'is_saleable'){
$this->getSelect()->order("is_saleable " . $dir);
return $this;
}
if ($this->isEnabledFlat()) {
$column = $this->getEntity()->getAttributeSortColumn($attribute);
if ($column) {
$this->getSelect()->order("e.{$column} {$dir}");
}
else if (isset($this->_joinFields[$attribute])) {
$this->getSelect()->order($this->_getAttributeFieldName($attribute).' '.$dir);
}
return $this;
} else {
$attrInstance = $this->getEntity()->getAttribute($attribute);
if ($attrInstance && $attrInstance->usesSource()) {
$attrInstance->getSource()
->addValueSortToCollection($this, $dir);
return $this;
}
}
return parent::addAttributeToSort($attribute, $dir);
}
}
The Configuration File:
/app/code/local/CustomMage/Catalog/etc
The file contains the instruction Magento needs to know what class we’ll be overriding.
<?xml version="1.0"?> <config> <modules> <MageDev_Catalog> <version>0.0.1</version> </MageDev_Catalog> </modules> <global> <models> <catalog_resource_eav_mysql4> <rewrite> <product_collection>CustomMage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection</product_collection> </rewrite> </catalog_resource_eav_mysql4> </models> </global> </config>
The Module Installer:
/app/etc/modules
Instruct Magento to run our new CustomMage module.
<?xml version="1.0"?> <config> <modules> <CustomMage_Catalog> <active>true</active> <codePool>local</codePool> <depends> <Mage_Catalog /> </depends> </CustomMage_Catalog> </modules> </config>
One last thing to mention, the solution on the forum appears to have limitations:
I just realized something.
It doesn’t work for bundle products with mandatory bundle items, when all the bundle items are out of stock and the main bundle product is in stock.
If someone finds a solution for this please post it here.
Hope that helped.