i need to sorting collection in two way in one collection. first i need to sort new product on top. out of stock product in last.

basically i need this solution by observer. I used this ...

my config...

<?xml version="1.0"?>
                    <filter_blank_image> // unique idrntifer

my observer

class Nine_Catalog_Model_Observer extends Varien_Event_Observer{

public function filterBlankImage(Varien_Event_Observer $observer)
    $collection = $observer->getEvent()->getCollection();

                array('_inventory_table' => $collection->getTable('cataloginventory/stock_item')),
                "_inventory_table.product_id = e.entity_id",
            ->order('is_in_stock DESC')
            ->order('created_at DESC');


  • If I understand you correctly you are trying to do something in one query but it can't be done. Please read dev.mysql.com/doc/refman/5.5/en/sorting-rows.html . If you are using ORDER BY created_at, is_in_stock and if there are some rows with same created_at date then it will be used is_in_stock to sort only these rows. You have to combine 2 collections or maybe to use UNION. – michael Dec 22 '14 at 8:25
  • Your observer seems to work fine for me - what is the actual issue you're seeing? when I run the same code, I get new products first, out of stock products last. – jharrison.au Jan 19 '15 at 6:39
  • @dkstack can you update us on your issue? Did you find a solution? – jharrison.au Jan 22 '15 at 21:50
  • Yes what exact problem do you have? What does your code produce. Besides a syntax error (too many closing braces at the end) I cannot see an issue with it. – Tobias Jan 25 '15 at 11:31

Is there a reason you need this in an observer? Heres what I did that might put you on the right path:

Extend Mage_Catalog_Model_Resource_Product_Collection to overwrite function addAttributeToSort

Just below

$storeId = $this->getStoreId();

Add the following: $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');

This will put out of stock products at the end of any product collection. If you call addAttributeToSort('created_at','DESC') on your collection it will sort by created_at but move out of stock products to the end of the collection. Same with any other attribute.

Full Module at github

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.