I want to show first all in-stock products and then out-of-stock products respectively and sort by price in the ascending order.
Following is my code. In my local.xml I wrote the following code for the price from low to high
<catalog_category_layered> <reference name="product_list"> <action method="setDefaultDirection"><dir>asc</dir></action> </reference> </catalog_category_layered>
and I wrote an observer to show all out-of-stock products last, i.e., after loading all in-stock products. For this I had used the catalog_product_collection_load_before event
$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);
Now what is happening is I can see the products from low to high but not out-of-stock products last.