Grid filter for columns with complex values

In previous articles we told you how to operate with columns in adminhtml grids. Usually the process is quite fast, for example, if you want to add simple column from the database. But sometimes you need to add a column with complex value, processed by renderer. You say: “it’s not a big deal to use renderers…” Right, until you face with sort and filter..

By default Magento applies grid filter directly to the corresponding column. But renderer might contain data from separate columns. At this point we need to choose the right way to make filter and sort work correctly.
For example, we are going to add a new column “Address” to the orders grid which consists of the values from few columns: city, street, postcode.
First of all, we should join address table to the collection:

$collection->joinLeft(
                array('sales_flat_order_address'),
                'sales_flat_order.billing_address_id = sales_flat_order_address.entity_id',
                array('postcode', 'street', 'city');

The second step is to add address column to the grid:

addColumn('address', array(
          'header'=> Mage::helper('sales')->__('Address'),
          'type'  => 'text',
));

Here we should connect column with the data. Let’s add ‘renderer‘ param:

addColumn('address', array(
          'header'=> Mage::helper('sales')->__('Address'),
          'type'  => 'text',
          'index' => 'city'
	  'renderer' => 'Atwix_Ordersgrid_Block_Adminhtml_Sales_Order_Renderer_Address'
));

Third, create the renderer:

class Atwix_Ordersgrid_Block_Adminhtml_Sales_Order_Renderer_Address
    extends Mage_Adminhtml_Block_Widget_Grid_Column_Renderer_Abstract
{
    public function render(Varien_Object $row)
    {
        $value = $row->getData('city') . ',' .
            $row->getData('street') . ',' .
            $row->getData('postcode');

        return $value;
    }
}

After these steps we are able to see the new column in the orders grid. But when you try to use filter on this column – you will get the result, filtered by ‘city’ db column, since we have added city as an index. To solve the problem we need to do one more thing: create callback method for the filter. The final view of addColumn calling is going to be like the following one:

$this->addColumn('address', array(
            'header'=> Mage::helper('sales')->__('Address'),
            'type'  => 'text',
		    'renderer' => 'Atwix_Ordersgrid_Block_Adminhtml_Sales_Order_Renderer_Address',
            'filter_condition_callback' => array($this, '_addressFilter'),
));

Note, that we have removed index field – we don’t need it anymore. And then, we create callback method which is described below:

protected function _addressFilter($collection, $column)
    {
        if (!$value = $column->getFilter()->getValue()) {
            return $this;
        }

        $this->getCollection()->getSelect()->where(
            "sales_flat_order_address.city like ?
            OR sales_flat_order_address.street like ?
            OR sales_flat_order_address.postcode like ?"
        , "%$value%");


        return $this;
    }

As you can see, we are using simple db query condition to filter query results by the columns. This way you can create your own filters for columns with complex values or custom renderers.
Feel free to ask any questions and suggest your own improvements. :)

You may also want to read:

• Adding SKU Column To Magento Orders Grid
• Inline Editing in Magento Backend Grids
• Adding Thumbnail Images to the Magento Admin Product Grid