How to do a ORDER BY with an expression in Magento Collections

collections, magento, sql, sql-order-by

Solution

Magento models have a `setOrder()` method for simple attribute sorting:

$oCollection = Mage::getModel('catalog/product')
    ->getCollection()
    ->setOrder('name_of_attribute_to_sort', 'ASC');

They don't have a dedicated method for sorting by expressions like yours, but Magento collections use a `Varien_Db_Select` instance extending `Zend_Db_Select`, so you could use its `order()` and `limit()` methods:

$oCollection = Mage::getModel('catalog/product')->getCollection();
$oCollection
    ->getSelect()
    ->order(array('SUBSTRING(field, 1, 2) DESC'))
    ->limit(10);

Problem

I already know how to do a simple order by with a Magento collection. But this time I want to do something like this, ``` SELECT * FROM table WHERE filed LIKE '%abc%' ORDER BY SUBSTRING(field, 1, 2) DESC LIMIT 10 ``` So how can I add SUBSTRING function to my order by clause? Any ideas?

Original source