Magento – Sorting a custom column – Customer Grid

columncustomer-gridmagento-1.9sorting

I have added in order count and last order date to the customer grid which is working fine, however it is not sortable or filterable in the grid.

I have done some searching but can't seem to find a solution that makes any sense, can someone explain how I can achieve this?

protected function _prepareCollection()
{
    $collection = Mage::getResourceModel('customer/customer_collection')
        ->addNameToSelect()
        ->addAttributeToSelect('email')
        ->addAttributeToSelect('created_at')
        ->addAttributeToSelect('group_id')
        ->joinAttribute('billing_postcode', 'customer_address/postcode', 'default_billing', null, 'left')
        ->joinAttribute('billing_city', 'customer_address/city', 'default_billing', null, 'left')
        ->joinAttribute('billing_telephone', 'customer_address/telephone', 'default_billing', null, 'left')
        ->joinAttribute('billing_region', 'customer_address/region', 'default_billing', null, 'left')
        ->joinAttribute('billing_country_id', 'customer_address/country_id', 'default_billing', null, 'left')
        ->joinAttribute('billing_company', 'customer_address/company', 'default_billing', null, 'left');

    $sql = 'SELECT COUNT(*) FROM sales_flat_order AS o WHERE o.customer_id=e.entity_id';

    $expr = new Zend_Db_Expr('(' . $sql . ')');

    $collection->getSelect()->from(null, array('orders_count'=>$expr));

    $sql ='SELECT MAX(o.created_at) FROM sales_flat_order AS o WHERE o.customer_id = e.entity_id ';
    $expr = new Zend_Db_Expr('(' . $sql . ')');

    $collection->getSelect()->from(null, array('last_order_date'=>$expr));

    $this->setCollection($collection);

    return parent::_prepareCollection();
}

//Rest of code omitted for brevity

protected function _prepareColumns()
{        
     //code omitted for brevity
     $this->addColumn('orders_count', array(
          'header'    => Mage::helper('customer')->__('Total Orders'),
          'align'     => 'left',
          'width'     => '40px',
          'index'     => 'orders_count',
          'type'  => 'number',
     ));

     $this->addColumn('last_order_date', array(
          'header'    => Mage::helper('customer')->__('Last Order Date'),
          'type'      => 'datetime',
          'align'     => 'center',
          'index'     => 'last_order_date',
          'gmtoffset' => true,
     ));
    //additional code omitted
}

Best Answer

Columns in Magento are filterable and sortable by default when they correspond to a column in the database, but because you are creating a column that doesn't directly correspond to a table in the database, you have to specify which data to filter and sort on.

To add the column into the filtering process, use the filter_condition_callback element in the column definition array. Specify it as a callable: array($this, 'functionName'), where functionName is replaced by a custom function you write to perform the filtering on the column. To specify the sort, specify the sort_index element, defining it as the column to sort on.

Related Topic