how to get number of orders made per customer in admin order grid

Question

I am trying to display number of orders made per customer (customer is identified by email) in admin order grid. Number of orders should be the sum of orders made per customer, regardless if customer is buying as a quest, or registered user. expected result should be like

increment_id count_orders email
1000001      1            [email protected]
1000008      2            [email protected]
1000015      3            [email protected]

not like this

increment_id count_orders email
1000001      3            [email protected]
1000008      3            [email protected]
1000015      3            [email protected]

What is the best approach to achieve this ?

Thank you!

Answer 1

Setting up an admin grid isn't too bad, take a look at this document. The biggest difference between what's in that article and what you want is you don't really need a custom model/resource mode/collection to get what you want, you can just use the Mage_Sales_Model_Resource_Order_Collection class instead. So in step 8 of the linked article (where they are setting up their grid class) you would want to use the sales order collection (Mage::getResourceModel('sales/order_collection')) instead of the custom one they are using. You will also probably want to set your columns as needed:

$orderCollection = Mage::getResourceModel('sales/order_collection');
// Resetting the columns first to get rid of main_table.* portion
// You can skip that part if you want all the columns returned in your collection 
// though
$orderCollection->getSelect()->reset(Zend_Db_Select::COLUMNS)->columns(['increment_id, 
'customer_email']);
// This will group it by customer email address which seems like the best way to tell
// if it's the same customer regardless of whether or not they logged in
$orderCollection->setOrder('customer_email');

To be honest I don't know how I would do the count_orders column the way you want it. The easiest way would be to do some post processing on the collection after loading it in PHP, but that seems almost stupidly inefficient and probably quite slow. I'm sure there is a way to do it in MYSQL, but I just don't know what it is. I have no idea if this will be helpful for you, but this might be a good place to start for that part.