Magento custom sort on grid – sort increment_id by numeric not alpha

You can easily filter column in a magento grid (see my previous post) but customize a sort is a little bit more hand made.

In this example in the order grid, I want to sort by increment_id, but this is a text fields and I want to sort it by numeric.

default sort : 1, 10, 100, 2, 3 …
numeric sort : 1, 2, 3, 10, 100 …

If you look the SQL request, you got this

SELECT `main_table`.* FROM `sales_flat_order_grid` AS `main_table` ORDER BY increment_id DESC LIMIT 20

To tell MySQL to sort this text column by numeric, you can add “+ 0″ to the ORDER BY clause, this will transform the text field into numeric field

SELECT `main_table`.* FROM `sales_flat_order_grid` AS `main_table` ORDER BY increment_id + 0 DESC LIMIT 20

Now, how to tell Magento to add “+ 0″ to this sort ?

Override your grid class and modify the “_setCollectionOrder($column)” function.

class Adin_Sales_Block_Adminhtml_Sales_Order_Grid extends Mage_Adminhtml_Block_Sales_Order_Grid {
    protected function _setCollectionOrder($column)
    {
        $collection = $this->getCollection();
        if ($collection) {
            $columnIndex = $column->getFilterIndex() ? $column->getFilterIndex() : $column->getIndex();
            if($columnIndex == 'increment_id')
            {
                $columnIndex = 'increment_id + 0';
            }
            $collection->setOrder($columnIndex, strtoupper($column->getDir()));
        }
        return $this;
    }
}

Magento custom filter on grid

Sometimes, you need a special column on your grid from another table or with some calculation.
It works well, column is displayed but filter doesn’t work.
Here a small example how to customize your filter :

On your grid class (Adin_Sponsorship_Block_Adminhtml_Sponsorship_Grid.class.php), override your _prepareColumns() function

protected function _prepareColumns()
{
        $this->addColumn('sponsor_email', array(
                'header'    => Mage::helper('evian_customer')->__('Sponsor Email'),
                'index'     => 'sponsor_email',
                'align'     => 'center',
                'filter_condition_callback' => array($this, '_filterSponsortEmail'),
            )
        );
}

The ‘filter_condition_callback’ option indicates to use as filter a specific method, on the current class ($this) the method _filterSponsortEmail().

So next step, create this method.

    protected function _filterSponsortEmail($collection, $column)
    {
        if (!$value = trim($column->getFilter()->getValue())) {
            return;
        }
        $this->getCollection()->addFieldToFilter('customer.email', array('like' => '%'.$value.'%'));
    }

Yes, this is this simple.

If you use a datetime column, it’s just a bit more complicated, see below

        $this->addColumn('created_at', array(
            'header'    => Mage::helper('evian_customer')->__('Date of sponsorship'),
            'index'     => 'created_at',
            'align'     => 'center',
            'type' => 'datetime',
            'filter_condition_callback' => array($this, '_filterDate'),
            )
        );

and the filter method

    protected function _filterDate($collection, $column)
    {
        $filters = $column->getFilter()->getValue();

            $from = $filters['from'];
            $to = $filters['to'];


       $this->getCollection()->addFieldToFilter('main_table.created_at', array('gteq' => $from->toString('yyyy-MM-dd')));
        $this->getCollection()->addFieldToFilter('main_table.created_at', array('lteq' => $to->toString('yyyy-MM-dd')));
    }

Magento filter products collection get only visible

You want to retrieve all your products but only the one visible in catalog or filter ?

You may use this

 $collection = Mage::getModel('catalog/product')->getCollection()->addAttributeToSelect('*');
 Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($collection);
 Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($collection);

But it’s depreacated and doesn’t work.
You may instead use this :

 $collection = Mage::getModel('catalog/product')->getCollection()->addAttributeToSelect('*');
 $collection->addFieldToFilter('visibility', Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH);