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')
, wherefunctionName
is replaced by a custom function you write to perform the filtering on the column. To specify the sort, specify thesort_index
element, defining it as the column to sort on.