How is admin grid sorted?

Question

I want to add "natural sorting" functionality to Magento admin grid. I would like to understand how the Magento handles admin grid sorting so I know where to start. What functions/files etc. does Magento use to determine the order of values in the admin panel?

Answer 1

With grids in Magento, the database driver controls the sorting of data. The collection is passed the sort column and direction of sort (asc or desc) or the default is used. The data in the collection returned is output in that same order.

To change the sorting functionality in your own custom grid, you can just alter modify the _prepareCollection method to adjust the collection to what order you want.

In my custom grid, I would probably do something like this:

class My_Module_Block_Adminhtml_Module_Index_Grid extends Mage_Core_Adminhtml_Block_Widget_Grid
{
    // ...

    protected function _prepareCollection() {
        $myCollection = Mage::getModel('my_module/model')->getCollection();
        $this->setCollection($myCollection);
        parent::_prepareCollection();
    }

    // ...

    protected function _afterLoadCollection() {
        usort($this->_collection, array($this, '_natSortCollection'));
        return parent::_afterLoadCollection();
    }

    protected function _natSortCollection($a, $b) {
        return strnatcmp($a->getField(), $b->getField());
    }
}

Edit

See the updated code below to do what you want.

protected function _afterLoadCollection()
{
    $items = $this->_collection->getItems();
    /* To prevent duplicate items, and to not reload from the database
       we have to remove each item individually without "clear()"ing
       the collection. */
    array_walk(array_keys($items), array($this->_collection, 'removeItemByKey'));
    usort($items, array($this, '_natSortCollection'));

    /* Now add each item back to the collection in the new order */
    array_walk($items, array($this->_collection, 'addItem'));
    return parent::_afterLoadCollection();
}

protected function _natSortCollection($itemA, $itemB)
{
    return strnatcmp($itemA->getName(), $itemB->getName());
}

Answer 2

From your comments in the previous answer, it may behoove you to use the MySQL SOUNDEX(<column_name>) function to naturally sort your data. You can even then add a LENGTH(<column_name>) call and finally <column_name>. For example:

    $this->getCollection()->addWebsiteNamesToResult();
    $this->getCollection()->getSelect()->reset(Zend_Db_Select::ORDER);
    $this->getCollection()->getSelect()->order(new Zend_Db_Expr(
        sprintf(
            'SOUNDEX(%1$s) %2$s, LENGTH(%1$s) %2$s, %1$s %2$s',
            $this->getParam($this->getVarNameSort(), $this->_defaultSort),
            $this->getParam($this->getVarNameDir(), $this->_defaultDir)
        )
    ));

This isn't perfect, but there isn't "natural sorting" in MySQL. So it's kind of as close as you can get to it. Using a separate method to alter the collection would add a layer of complexity and require work-arounds to make changing the sort column and direction to work 100% of the time.

This is just an alternative to the custom sort method above.