Magento: Use MySQL functions with addExpressionAttributeToSelect

Function addExpressionAttributeToSelect as defined in Mage_Eav_Model_Entity_Collection_Abstract (/app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php) allows us to use MySQL functions in Magento collection.

Here is an example of using MySQL function CONCAT in Magento customer collection to concat customer’s firstname and lastname:


$collection = Mage::getResourceModel('customer/customer_collection')
    ->addExpressionAttributeToSelect('fullname', 'CONCAT({{firstname}}, " ", {{lastname}})', array('firstname','lastname'));

Here is another example which uses MySQL function MONTH which fetches only month from customer’s birthdate.


$collection = Mage::getResourceModel('customer/customer_collection')
    ->addExpressionAttributeToSelect('birth_month', 'MONTH({{dob}})', 'dob')
    ->joinAttribute('dob', 'customer/dob', 'entity_id', null, 'left');

Hope it helps. Thanks.