how to add total number of orders to customer grid in magento

Question

i want to show number of orders in customer grid of magento

i used this as a guide: How to add customer "total number of orders" and "total spent" to order grid in magento 1.7

but this is a different grid

so far i have created: app/code/local/Mage/Adminhtml/Block/Customer/Grid.php

_prepareCollection

i added:

    $orderTableName = Mage::getSingleton('core/resource')
            ->getTableName('sales/order');

        $collection
            ->getSelect()
            ->joinLeft(
                array('orders' => $orderTableName),
                'orders.customer_id=e.entity_id',
                array('order_count' => 'COUNT(customer_id)')
            );
        $collection->groupByAttribute('entity_id');

before: $this->setCollection($collection);

_prepareColumns i added:

$this->addColumn('order_count', array(
            'header'    => Mage::helper('customer')->__('# orders'),
            'index'     => 'order_count',
             'type'  => 'number'
        ));

while it does work in the grid, i have some problems:

• the pager shows 1 customer (should be 500+)

• sorting on this new column doesn't work

Answer 1

Just remove:

$collection->groupByAttribute('entity_id');

And add this:

$collection->group('e.entity_id');

Overview we have:

$orderTableName = Mage::getSingleton('core/resource')
        ->getTableName('sales/order');

    $collection
        ->getSelect()
        ->joinLeft(
            array('orders' => $orderTableName),
            'orders.customer_id=e.entity_id',
            array('order_count' => 'COUNT(customer_id)')
        );
   $collection->group('e.entity_id');

OR

$orderTableName = Mage::getSingleton('core/resource')
        ->getTableName('sales/order');

    $collection
        ->getSelect()
        ->joinLeft(
            array('orders' => $orderTableName),
            'orders.customer_id=e.entity_id',
            array('order_count' => 'COUNT(customer_id)')
        )
        ->group('e.entity_id');

Answer 2

You have a GROUP BY clause in your collection, and the grid pager uses $collection->getSize() to determine the number of pages. The problem is that getSize() applies a SELECT COUNT(*) to the collection, and fetches the first column of the first row to get the number of results. With the GROUP BY still applied, the pager then considers that there is only one result.

To prevent this problem, you should either use your own customers collection with a relevant getSize(), or use sub-queries to retrieve the totals you need.

Answer 3

It's working fine over there. just follow the following steps .

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