Magento customer collection loading: how to fetch names attributes values

I know, I’m late publishing new topics: one per week is difficult when your evenings are busy…

But today, I’m leaving (temporary) my others stuffs to share with you something I’ve discover today: loading customer collection in Magento can be redundant in some cases if you use the addNameToSelect method. I’ll explain:

Case study: loading a collection

I’ll found this topic by doing a SQL query like this one in a Magento 1.6:

$customerCollection = Mage::getModel('customer/customer')->getCollection();
/* @var $customerCollection Mage_Customer_Model_Entity_Customer_Collection */
$customerCollection->addAttributeToSelect(array(
    'dob', 'firstname', 'lastname', 'email'
));
foreach ($customerCollection as $customer) {
    /* @var $customer Mage_Customer_Model_Customer */
    echo $customer->getFirstame() . ' ' . $customer->getLastname() . PHP_EOL;
} 

Query made by fetching a customer collection

Query is made with a first query which fetch matching entities:

SELECT `e`.* FROM `customer_entity` AS `e` WHERE (e.entity_type_id = '1');

customer data are hydrated by a dedicated query to fetch attributes:

SELECT 
     `customer_entity_datetime`.`entity_id`, 
     `customer_entity_datetime`.`attribute_id`, 
     `customer_entity_datetime`.`value` 
FROM `customer_entity_datetime` 
WHERE (entity_type_id=1) 
AND (entity_id in (324, 325, 326, 327, 328, 329, 330, 331, 332, 333, 334, 335, 337, 339, 340, 341, 342, 343, 344, 345)) AND (attribute_id in ('11')) 

UNION 

SELECT 
`customer_entity_varchar`.`entity_id`, 
`customer_entity_varchar`.`attribute_id`, 
`customer_entity_varchar`.`value` 
FROM `customer_entity_varchar` 
WHERE (entity_type_id=1) 
AND (entity_id in (324, 325, 326, 327, 328, 329, 330, 331, 332, 333, 334, 335, 337, 339, 340, 341, 342, 343, 344, 345)) AND (attribute_id in ('5', '7'))

Customer collection loaded with addNameToSelect method

It seems that addNameToSelect method change how is builded the query. We can test the expected query with the following test code:

$customerCollection = Mage::getModel('customer/customer')->getCollection();
/* @var $customerCollection Mage_Customer_Model_Entity_Customer_Collection */
$customerCollection->addNameToSelect();
$customerCollection->addAttributeToSelect(array(
    'dob', 'firstname', 'lastname', 'email'
));
foreach ($customerCollection as $customer) {
    /* @var $customer Mage_Customer_Model_Customer */
    echo $customer->getFirstame() . ' ' . $customer->getLastname() . PHP_EOL;
} 

With the same script, we have the following queries mades:

SELECT `e`.*, 
`_table_prefix`.`value` AS `prefix`, 
`_table_firstname`.`value` AS `firstname`, 
`_table_middlename`.`value` AS `middlename`, 
`_table_lastname`.`value` AS `lastname`, 
`_table_suffix`.`value` AS `suffix`, 
CONCAT(IF(_table_prefix.value IS NOT NULL AND _table_prefix.value != "", CONCAT(TRIM(_table_prefix.value)," "), ""),TRIM(_table_firstname.value),IF(_table_middlename.value IS NOT NULL AND _table_middlename.value != "", CONCAT(" ",TRIM(_table_middlename.value)), "")," ",TRIM(_table_lastname.value),IF(_table_suffix.value IS NOT NULL AND _table_suffix.value != "", CONCAT(" ",TRIM(_table_suffix.value)), "")) AS `name` 

FROM `customer_entity` AS `e`
 LEFT JOIN `customer_entity_varchar` AS `_table_prefix` ON (`_table_prefix`.`entity_id` = `e`.`entity_id`) AND (`_table_prefix`.`attribute_id` = '4')
 LEFT JOIN `customer_entity_varchar` AS `_table_firstname` ON (`_table_firstname`.`entity_id` = `e`.`entity_id`) AND (`_table_firstname`.`attribute_id` = '5')
 LEFT JOIN `customer_entity_varchar` AS `_table_middlename` ON (`_table_middlename`.`entity_id` = `e`.`entity_id`) AND (`_table_middlename`.`attribute_id` = '6')
 LEFT JOIN `customer_entity_varchar` AS `_table_lastname` ON (`_table_lastname`.`entity_id` = `e`.`entity_id`) AND (`_table_lastname`.`attribute_id` = '7')
 LEFT JOIN `customer_entity_varchar` AS `_table_suffix` ON (`_table_suffix`.`entity_id` = `e`.`entity_id`) AND (`_table_suffix`.`attribute_id` = '8') WHERE (e.entity_type_id = '1');

SELECT `customer_entity_datetime`.`entity_id`, 
`customer_entity_datetime`.`attribute_id`, 
`customer_entity_datetime`.`value` 
FROM `customer_entity_datetime` 
WHERE (entity_type_id=1) AND (entity_id in (324, 325, 326, 327, 328, 329, 330, 331, 332, 333, 334, 335, 337, 339, 340, 341, 342, 343, 344, 345)) AND (attribute_id in ('11')) 

UNION 

SELECT `customer_entity_varchar`.`entity_id`, 
`customer_entity_varchar`.`attribute_id`, 
`customer_entity_varchar`.`value` 
FROM `customer_entity_varchar` 

WHERE (entity_type_id=1) AND (entity_id in (324, 325, 326, 327, 328, 329, 330, 331, 332, 333, 334, 335, 337, 339, 340, 341, 342, 343, 344, 345)) AND (attribute_id in ('5', '7'));	

In this case, customer name values are fetched two times: one time with a join method, a second time with the attribute query, so we do job two times… 🙁

Conclusion on addNameToSelect method

addNameToSelect should be used only if you do not fetch any data from varchar backend storage. if not, you should not use these method for fetching names attributes because you’ll do the job two times: Perhaps in this case it could be more interesting to build “name” value, based upon on the customer_entity_varchar attribute scan query

This test has been made on 1.6