List Products In Magento By Price But Have Zero Prices At The End

Question

Is there a way in the product search sorting list to order the products by highest price to lowest price but then have products with zero price displayed at the end.

The normal sorting works fine (low to high or high to low) but would really like the ability to include the zero price products at the end, an example of how it would be listed would be:

Product 1: £3

Product 2: £18

Product 3: £0

Product 4: £0

I haven't found a way from searching here or Google yet and not being too familiar with Magento I am not sure where I would look to change this. If anyone has an answer or can point me to the query or correct file for me to edit myself I don't mind having a go myself.

Using some help from here and other questions I have edited the _getProductCollection() method inside the file /app/code/core/Mage/Catalog/Block/Product/List.php (don't worry I've copied to local) and added these few lines:

$orderFilterType = $this->getRequest()->getParam('order');
$dirFilterType = $this->getRequest()->getParam('dir');

if( isset( $orderFilterType ) && $orderFilterType == 'price' && isset( $dirFilterType ) && $dirFilterType == 'asc' ) {

$this->_productCollection = $layer->getProductCollection()->addAttributeToSort( 'price', 'DESC' );


} else { 

$this->_productCollection = $layer->getProductCollection();

}

This means that whatever I do inside this code will only run once someone has selected the orderby price dropdown with the ascending option.

The problem is now is I'm not sure what to do to affect the value for $this->_productCollection = $layer->getProductCollection(); to make this return as I want.

Answer 1

4 years late to the party I know but I've just solved this in a much nicer way than the other solutions I found, might help someone.

In Catalog/Block/Product/List/Toolbar.php replace

$this->_collection->setOrder($this->getCurrentOrder(), $this->getCurrentDirection());

with

$zeroPriceLast = new Zend_Db_Expr('`price_index`.`price` = 0 ASC, `price_index`.`price`  ASC');

$this->_collection->getSelect()->order($zeroPriceLast);

and wrap in a conditional so that the new logic is only applied when sorting by price.

Answer 2

Have a look here at this question: Magento, custom product list this might fit your need

Answer 3

To do this in raw SQL would look like:

SELECT main.*, price.value as price FROM catalog_product_entity AS main
    JOIN catalog_product_entity_decimal AS price ON price.entity_id = main.entity_id
    JOIN eav_attribute AS attr_price ON attr_price = 'price' AND price.attribute_id = attr_price.attribute_id
ORDER BY price == 0, price

You may have to override a core block in order to get the ORDER BY price ==0, price clause working.

Answer 4

I think you need something like this

1. Create an observer for each product list:

   <events> <catalog_block_product_list_collection> <observers> <rebuild_collection> <type>singleton</type> <class>XXX_YYY_Model_Catalog_Observer</class> <method>rebuildCollection</method> </rebuild_collection> </observers> </catalog_block_product_list_collection></events>

where XXX - your namespace YYY - your module

1. create php file

   <?php
   class XXX_YYY_Model_Catalog_Observer extends Mage_Catalog_Model_Observer
   {
   public function rebuildCollection($observer)
   {
   $event = $observer->getEvent();
   /** @var $collection Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection */
   $collection = $event->getCollection();
   
   $toolbar = Mage::getBlockSingleton('catalog/product_list_toolbar');
   
           // here you can add some attributes to collection but it's not required
           // ex. $collection->addAttributeToSelect('brand')
           //    ->addAttributeToSelect('style');
           // you can also force visibility filter Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($collection);
   
            // and here is main part for your logic
           // getting current sort order
           $arr = $collection->getSelect()->getPart(Zend_Db_Select::ORDER);
           // probably here you will need to add condition like  IF ORDERING BY PRICE
           $r = Mage::app()->getRequest()->getRequestString();
   
           $order_field = 'new_price_for_sort';
           $collection->getSelect()->joinLeft(array('a' => 'catalog_product_entity_price'), // or here you will need Price Index Table Name if you have Groupped or configurable products. But need to remember that non-simple products always have 0 price and MAX_PRICE/MIN_PRICE should be used then 
                           'a.product_id=e.entity_id AND a.attibute_id=PRICE or SPECIAL PRICE ATTRIBUTE ID', // or here you will not require attribute_id condition if you are using INDEX TABLE
                           array(
                               'new_price_for_sort' => new Zend_Db_Expr('IF(a.value > 0,a.value, 9999999)'), // which mean that in new field we will have price if it's not 0, and will have big integer is real price is zero. 
                           ));
                   }
   // now we are reseting current order by
                   $collection->getSelect()->reset(Zend_Db_Select::ORDER);
   
   // and creating new order by new field, but with saving direction of order
   $dir = $arr['dir'];
                   if (!$collection->isEnabledFlat()) {
                       $collection->setOrder($order_field, $dir);
                   } else {
                       $collection->getSelect()->order($order_field . ' ' . $dir);
                   }
   
   
               }
         //check in log if collection real built well
           Mage::log((string)$collection->getSelect(), null, 'selects.log');
         // add collection to list toolbar
           $toolbar->setCollection($collection);
       }
   return $collection;
   }
   }
   

PS sorry, I have not rechecked this code properly, but have copied most of it from real working observer. But there can be some misstypes or missed chars )