How to Show Total Number of Orders for Each Customer in Magento Admin Grid
Are you looking to display the total number of orders for each customer directly in the customer grid within your Magento admin panel? You’re not alone - this is a common request from merchants who want to quickly identify their top customers and track order history.
In this post, I’ll guide you through adding a new column to show the total number of orders per customer. We’ll also address some common challenges like sorting and pagination that often arise during this process.
Setting Up the Customer Grid with Order Count
Let’s start by creating a custom version of the customer_grid collection that includes the order count for each customer.
- Create the Custom Collection Method
In your local copy of app/code/local/Mage/Adminhtml/Block/Customer/Grid.php, add a new method called _prepareCollection():
protected function _prepareCollection()
{
$collection = Mage::getModel('customer/customer')->getCollection();
// Join the sales/order table to count orders per customer
$orderTableName = Mage::getSingleton('core/resource')
->getConnection(Mage_Core_Model_Resource_Db_Abstract::CONNECTION_LOCAL)
->getTableName('sales/order');
$collection->getSelect()
->joinLeft(
array('orders' => $orderTableName),
'orders.customer_id=e.entity_id',
array('order_count' => new Zend_Db_Expr('COUNT(customer_id)'))
);
return $collection;
}
This code joins the sales/order table to your customer collection and adds a new column called order_count that contains the total number of orders for each customer.
- Add the New Column to the Grid
Next, in your _prepareColumns() method, add a new column for the order count:
$this->addColumn('order_count', array(
'header' => Mage::helper('customer')->__('# Orders'),
'index' => 'order_count',
'type' => 'number'
));
This creates a new column called ”# Orders” that displays the total number of orders for each customer.
- Update the Collection and Set the Page Size
To fix the pagination issue you mentioned, make sure to set the page size before calling $this->setCollection($collection);:
$collection->setPageSize(50);
$this->setCollection($collection);
Setting a reasonable page size ensures that your grid displays all customers without overloading the server.
Sorting and Order Count Column
To enable sorting by the order count column, you’ll need to add a custom indexer for your customer collection.
- Create a Custom Indexer
In your local copy of app/code/local/Mage/Adminhtml/Model/Resource/Customer/Collection.php, add a new indexer:
public function getSelectCountSql()
{
$countSql = parent::getSelectCountSql();
$countSql->reset(Zend_Db_Select::GROUP);
return $countSql;
}
This custom indexer resets the GROUP clause that was added by our previous code. This ensures that Magento can count the total number of customers correctly, even with the additional order count column.
- Update the Order Count Column Sorting
Finally, update your _prepareColumns() method to allow sorting by the order_count column:
$this->addColumn('order_count', array(
'header' => Mage::helper('customer')->__('# Orders'),
'index' => 'order_count',
'type' => 'number',
'filter_index' => 'main_table.order_count'
));
Adding the filter_index parameter ensures that Magento can correctly sort and filter the grid using the order_count column.
Conclusion: Displaying Order Count in Customer Grid
Displaying the total number of orders for each customer in your Magento admin grid provides valuable insights to your sales team. By following this guide, you can easily add a new column to the customer grid that shows order count and ensures proper pagination and sorting functionality.
As always, remember to test your changes thoroughly before deploying them to your live production environment.
If you found this tutorial helpful, I encourage you to check out our other Magento development guides at Laravel Company.
Happy coding!