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.

Menu

As you can see on the picture bellow, I decided to disable filtering options, sorting by column and Massaction Block.

Latest 5 Customers who purchased something

CustomerCollection extension is tested on Magento CE 1.6.1.0. You can download extension here.

Cheers.

You made it all the way down here so you must have enjoyed this post! You may also like:

Get the current customer role in Magento Petar Sambolek
Petar Sambolek, | 4

Get the current customer role in Magento

Magento Switchable Install Script Setup Class Damir Korpar
, | 5

Magento Switchable Install Script Setup Class

Remind customers to place their first order in Magento Marko Martinovic
Marko Martinovic, | 17

Remind customers to place their first order in Magento

14 comments

  1. i tried this (magento 1.9.0.1) and get 404 whenever i load any of the collections as well. Any help on that?

  2. 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.

  3. 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.

  4. 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

  5. Thanks, great post!
    Just as a addition, it’s also possible to use

    ->addAttributeToFilter('confirmation', array('notnull' => true))

    instead of ->joinField(‘notconfirmed’, …) in the first example.
    Keep up the great work!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <blockquote cite=""> <code> <del datetime=""> <em> <s> <strike> <strong>. You may use following syntax for source code: <pre><code>$current = "Inchoo";</code></pre>.