Ttdfjt

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

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.

My solution

need override method - _setCollectionOrder

will like

protected function _setCollectionOrder($column)

 if (!$dir = $column->getDir()) 
 return $this;
 

 if($column->getIndex()=='orders_count') 
 mage::log($column->getIndex());
 $collection = $this->getCollection();

 $collection->getSelect()
 ->order("orders_count " . strtoupper($column->getDir()));
 return $this;
 

 if($column->getIndex()=='orders_total') 
 mage::log($column->getIndex());
 $collection = $this->getCollection();

 $collection->getSelect()
 ->order("orders_total " . strtoupper($column->getDir()));
 return $this;
 


 return parent::_setCollectionOrder($column);