Issues sorting by two attributes

Question

I'm having problems applying a default sort order to the product collection for the listing page. When some conditions are met I want to sort by 'region' and then by 'price'. I have added the following in app/code/local/Mage/Catalog/Block/Product/List/Toolbar.php.

  public function setCollection($collection)
{
    $this->_collection = $collection;

    $this->_collection->setCurPage($this->getCurrentPage());
    $limit = (int)$this->getLimit();
    if ($limit) {
        $this->_collection->setPageSize($limit);
    }
    if(Mage::registry('current_category')->getName() == 'France'){
        $this->_collection->setOrder(array('region', 'price'), $this->getCurrentDirection());
    }else{
        if ($this->getCurrentOrder()) {
            $this->_collection->setOrder($this->getCurrentOrder(), $this->getCurrentDirection());
        }
    }
    return $this;
}

This sorts by region successfully but not by price. I did read somewhere that different resources are used by price than other attributes when using them for sorting. If this is the case it makes sense because when I substituted price with name - $this->_collection->setOrder(array('region','name'), 'asc'); I get the collection sorted by region and then by name.

To confirm, both attributes are set yes to - Used in Product Listing and Used for Sorting in Product Listing. Any suggestions on getting price to work with region?

Answer 1

I think the problem here is that there are some special code that needs to be run to sort by price.

If you take a look into the function setOrder on the model Mage_Catalog_Model_Resource_Product_Collection you will see that it does something "special" when dealing with the attribute price.

public function setOrder($attribute, $dir = 'desc')
{
    if ($attribute == 'price') {
        $this->addAttributeToSort($attribute, $dir);
    } else {
        parent::setOrder($attribute, $dir);
    }
    return $this;
}

My suggestion for you is to follow this example and try adding the sort by price in the same fashion and by calling addAttributeToSort.

Answer 2

After going through Mage_Catalog_Model_Resource_Product_Collection as @DavidManners suggested I eventually found a solution that appears to work

   $this->_collection->getSelect()->order(array('region', 'price_index.final_price'), $this->getCurrentDirection());