Playing with customer collection in Magento

In this article I’ll give you some example how to get different collection of customers.
For example, how to get latest 5 customers, unconfirmed customers, latest 5 customers who purchased something etc.
I’ll create few objects that are instances of Mage_Customer_Model_Resource_Customer_Collection class and few objects that are instances of Inchoo_CustomerCollection_Model_Resource_Customer_Totals_Collection class.
Class Inchoo_CustomerCollection_Model_Resource_Customer_Totals_Collection extends Mage_Reports_Model_Resource_Customer_Totals_Collection class.
Let’s take a look.
How to get Not confirmed customers?
$collection = Mage::getResourceModel('customer/customer_collection')
->addNameToSelect()
->joinAttribute('billing_postcode', 'customer_address/postcode', 'default_billing', null, 'left')
->joinAttribute('billing_city', 'customer_address/city', 'default_billing', null, 'left')
->joinAttribute('billing_telephone', 'customer_address/telephone', 'default_billing', null, 'left')
->joinAttribute('billing_region', 'customer_address/region', 'default_billing', null, 'left')
->joinAttribute('billing_country_id', 'customer_address/country_id', 'default_billing', null, 'left')
->joinField('notconfirmed',
'customer_entity_varchar',
'attribute_id',
'entity_id=entity_id',
"{{table}}.attribute_id='831'");
$this->setCollection($collection);
return parent::_prepareCollection();
The last parameter of joinField is string which contain table, field and value “831”. Value “831” is ‘attribute_id’ from ‘eav_attribute’ table where ‘attribute_code’ is equal “confirmation”.
How to get customers with Gmail account?
$collection = Mage::getResourceModel('customer/customer_collection')
->addNameToSelect()
->joinAttribute('billing_postcode', 'customer_address/postcode', 'default_billing', null, 'left')
->joinAttribute('billing_city', 'customer_address/city', 'default_billing', null, 'left')
->joinAttribute('billing_telephone', 'customer_address/telephone', 'default_billing', null, 'left')
->joinAttribute('billing_region', 'customer_address/region', 'default_billing', null, 'left')
->joinAttribute('billing_country_id', 'customer_address/country_id', 'default_billing', null, 'left')
->addFieldToFilter('email',array('like'=>'%@gmail.com'));
$this->setCollection($collection);
return parent::_prepareCollection();
How to get customer with the largest purchased amount?
$collection is instance of Inchoo_CustomerCollection_Model_Resource_Customer_Totals_Collection. Methods: addTotalOrderAmount(), addBillingData() and addCustomerData() are declared in Inchoo_CustomerCollection_Model_Resource_Customer_Totals_Collection class.
public function __construct()
{
parent::__construct();
$this->setId('customerGrid');
$this->setPagerVisibility(false);
$this->setFilterVisibility(false);
$this->setDefaultLimit(1);
}
protected function _prepareCollection()
{
$collection = Mage::getResourceModel('customercollection/customer_totals_collection');
$collection->joinCustomerName()
->groupByCustomer()
->addTotalOrderAmount()
->addBillingData()
->addCustomerData()
->orderByTotalAmount();
$this->setCollection($collection);
return parent::_prepareCollection();
}
These collections of customers, You can see under Customer->Customer Collection.
As you can see on the picture bellow, I decided to disable filtering options, sorting by column and Massaction Block.
CustomerCollection extension is tested on Magento CE 1.6.1.0. You can download extension here.
Cheers.
14 comments
i tried this (magento 1.9.0.1) and get 404 whenever i load any of the collections as well. Any help on that?
Nice post but if I use this the sql statement take long time to run approx 16-19 seconds. I have around 2 Lakhs customers. if I simply remove the filter then it takes only 0.0012 seconds. Why any Index needed. Please help me to run it faster.
Not working on CE 1.9.2… I was expecting to see the not confirmed customers only!!
Plese i have not understand why in use “_prepareColumns()” in magento
I want to get customer of a particular date. How can I do that
Yes, I found it, but it was sort of a workaround. I extended the collection adding a method to do outer joins, but I haven’t the code anymore. And it didn’t play really well with all the magento filters and other features.
@Andrea Have you found a solution? I’ts also driven me crazy 🙁
Please, help me. I’m fighting with this since yesterday. I need to filter out the not confirmed customers.
->addAttributeToFilter(‘confirmation’, array(‘null’ => true)) doesn’t work. I understand that the problem is that addAttributeToFilter does a inner join, and I need an outer join. As far as I can tell is impossible to do (natively) an outer join in Magento.
Any ideas? Please, I’m going crazy
Total Order Amount in Customers with largest amount
is not include discount from price. how to fix it.
thanks you.
After installation of your module I get 404 page too, what is wrong ?
Is this extension work on Magento 1.5.x ?
thanks but when I click on customer menus it returns to page 404 . can you help me?
Thanks, great post!
Just as a addition, it’s also possible to use
instead of ->joinField(‘notconfirmed’, …) in the first example.
Keep up the great work!
Put the dog back into the box. 🙂