Sorting a custom column - Customer Grid

Question

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
}

Alex · Answer

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);
}

tjons · 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.

Sorting a custom column - Customer Grid

2 Answers

Add your own answers!

Ask a Question