By default Magento has sorting by Name ASC OR DESC.

In my case, I have products names starting with numbers and Alphabets like Below and currently my product listing page lists products in this order.

.8" stand for all
07" to 09" stand for all
Madame First
Alexander First

I Need my output on the Product listings page to be like below.

Alexander First
Madame First
.8" stand for all
07" to 09" stand for all

After searching for a way in MYSQL, I have found that we need to use REGEX in ORDER BY.

I don't know how to use this in Magento 1.8.1. Can Any one suggest a good solution please?


File: app\code\local\Amasty\Sorting\Block\Catalog\Product\List\Toolbar.php (extends Mage_Catalog_Block_Product_List_Toolbar)

Method: setCollection


    if ($this->getCurrentOrder() != 'name'){

    $select = $collection->getSelect();

    $field = new Zend_Db_Expr("name REGEXP '^[a-zA-Z]' DESC");

    // move to the first position
    $orders = $select->getPart(Zend_Db_Select::ORDER);
    if (count($orders) > 1){
        $last = array_pop($orders);
        array_unshift($orders, $last);
        $select->setPart(Zend_Db_Select::ORDER, $orders); 
  • Thanks for the solution. Working just like I Expected. I would be helpful if you can explain the (if) condition. Why we need to move to first position ?
    – Franc
    Oct 11 '14 at 13:16
  • it's because we are modifying the ready SQL query (collection), that already has order by part set to sort by name. Now we want to add additional sorting before name.
    – Amasty
    Oct 13 '14 at 10:19

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.