How to add column for total orders in customer grid in magento?

Question

I have added total number of orders column to customer grid in the magento admin. I need to put 2 text box in that column like from and to so we can search customers whose total orders are between that given range.

ex. suppose we put 3 and 5 into (form and to) textbox then we will display all customers whose total orders are between 3 to 5.

and also need to work filtering (searching and sorting)

---

add code in the following file app\code\core\Mage\Adminhtml\Block\Customer\Grid.php

add this code in _prepareCollection() fucntion only

$sql ='SELECT COUNT(*)'
 . ' FROM ' . Mage::getSingleton('core/resource')->getTableName('sales/order') . ' AS o'
 . ' WHERE o.customer_id = e.entity_id ';
 $expr = new Zend_Db_Expr('(' . $sql . ')'); 
 $collection->getSelect()->from(null, array('orders_count'=>$expr));

---

and also add this code in _prepareColumns() function with same file

$this->addColumn('orders_count', array(
 'header' => Mage::helper('customer')->__('Total Orders'),
 'align' => 'left',
 'width' => '40px',
 'index' => 'orders_count',
 'type' => 'number',
 'sortable' => true,
 ));

• Charlie: What is the datatype you used for total number of orders field?
• Sandip Baviskar: 'type' => 'number'
• Charlie: I think below answer helps you lot, Did you tried them?

Answer 1

According to your Question asked, it seems like you have successfully added a Grand Total Column, you just need to add range boxes, that can help in filter.

Find the code below to add in customer Grid -

$this->addColumn('orders_count', array(
 'header' => Mage::helper('customer')->__('Orders Count'),
 'width' => '50px',
 'index' => 'orders_count',
 'type' => 'number',
 ));

[EDITED]

I have gone little bit deep in this Question and find that we can provide filter in the following manner -

$this->addColumn('orders_count', array(
 'header' => Mage::helper('customer')->__('Orders Count'),
 'align' => 'left',
 'width' => '40px',
 'index' => 'orders_count',
 'type' => 'number',
 'sortable' => true,
 'filter_condition_callback' => array($this, '_filterHasOrderTotalBetweenCallback'),
 ));

and add the following function -

public function _filterHasOrderTotalBetweenCallback($collection, $column){

 if (!$value = $column->getFilter()->getValue()) {
 return $this;
 }
 if (!empty($value)) {
 $from = $value['from'];
 $to = $value['to']; 
 $this->getCollection()->getSelect()->having(
 "orders_count>=$from AND orders_count<=$to");
 Mage::log((string)$this->getCollection()->getSelect(),null,'customer.log');
 } 
 return $this;
}

Adding the above function is providing the result in the form of query which is running fine in MYSQL , but not accepted in Magento

• Sandip Baviskar: i already display total orders are placed by customers in the customer grid. now we need to put 2 text boxes in that column .we can search customers whose total orders are between that given range. ex. suppose we enter 3 and 5 into (form and to) text box then we will display customers whose total orders are between 3 to 5 in that customer grid. and also need to work on filtering
• Amit Dwivedi: @sandip Baviskar, I have edited my answer please proceed with it

Answer 2

you need to add 'type' => 'number', into your Grid.php file. Example of customer_id:

 $this->addColumn('entity_id', array(
 'header' => Mage::helper('customer')->__('ID'),
 'width' => '50px',
 'index' => 'entity_id',
 'type' => 'number',
 ));

• Sandip Baviskar: i already display total orders are placed by customers in the customer grid. now we need to put 2 text boxes in that column .we can search customers whose total orders are between that given range. ex. suppose we enter 3 and 5 into (form and to) text box then we will display customers whose total orders are between 3 to 5 in that customer grid. and also need to work on filtering