4

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 ?

8


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.

  • I am not able to filter it according to the count. It is filterable only according to the customer_id. How can this be rectified ? – Rajat Agarwal Mar 4 '17 at 11:22
  • @RajatAgarwal, Updated answer which will work with filter and sorting. – Jaimin Sutariya Mar 4 '17 at 16:47
  • One last question sir.. How can I display the same in the customer grid ? – Rajat Agarwal Mar 6 '17 at 6:48
  • Its working fine.. but I am not able to filter the field – Rajat Agarwal Mar 6 '17 at 7:10
  • How can I make this field filterable in customer grid ? @JaiminSutariya – Rajat Agarwal Mar 6 '17 at 8:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.