Custom Sorting for Product collection based on position, best seller and product created date

Question

I have to implement following scenario.

Add one new sort option named as New & Best Selling. When sort by this option it should follow below rule:

1. display the product which has position greater than 1

2. product based on best seller in descending order

3. remaining product based on it's created date in descending order.

Can anyone please guide me ?

Answer 1

I have fixed this issue.

1) Add new option by override Mage_Catalog_Model_Config , in this use getAttributeUsedForSortByArray function for add option

public function getAttributeUsedForSortByArray()
{
   foreach ($this->getAttributesUsedForSortBy() as $attribute) {
        $options[$attribute->getAttributeCode()] = $attribute->getStoreLabel();
    }
    $options['new_bestselling'] = Mage::helper('catalog')->__('New & Best Selling');

    return $options;
}

2) Add new option by override Mage_Adminhtml_Model_System_Config_Source_Catalog_ListSort in your custom module modify toOptionArray function

public function toOptionArray()
{
    $options = array();

    foreach ($this->_getCatalogConfig()->getAttributesUsedForSortBy() as $attribute) {
        $options[] = array(
            'label' => Mage::helper('catalog')->__($attribute['frontend_label']),
            'value' => $attribute['attribute_code']
        );
    }

    $options[] = array(
        'label' => Mage::helper('catalog')->__('New & Best Selling'),
        'value' => 'new_bestselling'
    );

    return $options;
}

3) Main logic is built in toolbar.php file for that override Mage_Catalog_Block_Product_List_Toolbar in your custom module and modify setCollection function .

public function setCollection($collection)
{
    parent::setCollection($collection);

    if ($this->getCurrentOrder()) {
        if($this->getCurrentOrder() == 'new_bestselling') {
              $this->getCollection()->getSelect()
                 ->joinLeft(
                        array('sfoi' => $collection->getResource()->getTable('sales/order_item')),
                         'e.entity_id = sfoi.product_id',
                         array('qty_ordered' => 'SUM(sfoi.qty_ordered)')
                     )
                 ->group('e.entity_id');
                 $this->getCollection()->getSelect()->Order(array(new Zend_Db_Expr('cat_index_position = 0,cat_index_position ASC')));
                 $this->getCollection()->getSelect()->Order(array(new Zend_Db_Expr('qty_ordered = NULL,qty_ordered DESC')));
                 $this->getCollection()->getSelect()->Order(array(new Zend_Db_Expr('e.created_at DESC')));
        } 

        else {
             $this->getCollection()
                 ->setOrder($this->getCurrentOrder(), $this->getCurrentDirection())->getSelect();
        }
    }

    return $this;
}