No. of orders placed by customer in order and customer grid

by

The question:

I want to display the total no. of orders placed by the customer in the order and customer grid. This will help me identify the type of customer.

Grid.php

protected function _prepareCollection()
{
    $collection = Mage::getResourceModel($this->_getCollectionClass());

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


    $this->setCollection($collection);
    return parent::_prepareCollection();
}    
protected function _prepareColumns()
{
        $this->addColumn('orders_count', array(
        'header'    => Mage::helper('customer')->__('Total No. of Orders'),
        'align'     => 'left',
        'width'     => '40px',
        'index'     => 'orders_count',
        'type'  => 'number',
        'sortable' => true,
    ));
 }

This shows me the total no. of orders placed and not customer wise.

How can I show the no. of orders customer wise in the order as well as customer grid ?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

Try below code.

Grid.php

protected function _prepareCollection()
{
    $collection = Mage::getResourceModel($this->_getCollectionClass());
    $readAdapter = Mage::getSingleton('core/resource')->getConnection('core_read');
    $select = $readAdapter->select()
            ->from('sales_flat_order_grid')
            ->columns(array('orders_count' => new Zend_Db_Expr('COUNT(*)'))
    )->group('customer_id');
    $collection->getSelect()->joinLeft(
        array('orders_count' => new Zend_Db_Expr('('.$select.')')),
        'main_table.customer_id = orders_count.customer_id',
        array('orders_count.orders_count')
    );
    $collection->addFilterToMap('orders_count', 'orders_count.orders_count');
    $this->setCollection($collection);
    return parent::_prepareCollection();
}    
protected function _prepareColumns()
{
        $this->addColumn('orders_count', array(
        'header'    => Mage::helper('customer')->__('Total No. of Orders'),
        'align'     => 'left',
        'width'     => '40px',
        'index'     => 'orders_count',
        'type'  => 'number',
        'sortable' => true,
    ));
 }

To add orders count to Customer Grid,
add below code in your _prepareCollection() function before you set collection on line $this->setCollection($collection);

$readAdapter = Mage::getSingleton('core/resource')->getConnection('core_read');
$select = $readAdapter->select()
            ->from('sales_flat_order_grid')
            ->columns(array('orders_count' => new Zend_Db_Expr('COUNT(*)'))
    )->group('customer_id');
    $collection->getSelect()->joinLeft(
        array('orders_count' => new Zend_Db_Expr('('.$select.')')),
        'e.entity_id = orders_count.customer_id',
        array('orders_count.orders_count')
    );
$collection->addFilterToMap('orders_count', 'orders_count.orders_count');

And add your column just the same with filter_condition_callback,

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

And Create a function in the same file,

protected function _ordersCountFilterCallBack($collection, $column)
{
    if (!$value = $column->getFilter()->getValue()) {return $this;}
    $from = ($value['from']) ? $value['from']: 0;
    $to = ($value['to']) ? $value['to'] : 999999;
    $collection->getSelect()->where("orders_count.orders_count >= $from AND  orders_count.orders_count <= $to");
    return $this;
}

NOTE : I have disable sorting for this field because I did not get time for it. You can try it by Your self or I will update once I get time for it.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment