Playing with customer collection in Magento

Featured Image

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.


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