Mini Tutorial: How to use MYSQL String Functions to filter a Magento collection

MySQL string functions such as LOWER/UPPER, REPLACE, CONCAT, etc., provide a way to do a flexible search on the database. Since Magento uses collections to generate SELECT statements and store the returned rows, the MySQL string functions to be used must be added right after the: getColllection()function.

The addExpressionAttributeToSelect() function allows the usage of those MYSQL functions on a Magento collection. This adds a new attribute for the collection where the value is the result of the MySQL functions used on existing attributes.

Let’s say you want to retrieve a customer with a first and last name of “John Doe” from a customer collection. You want it to be a case-insensitive filter by using the LOWER and CONCAT MySQL functions:

$fullNameFilter = ‘John Doe’;
$customers = Mage::getModel(‘customer/customer’)->getCollection()
->addExpressionAttributeToSelect(‘full_name’,’LOWER(CONCAT({{firstname}},”  “,{{lastname}}))’, array(‘firstname’,’lastname’)) //** Step 1: create the new attribute/column – combination of first name<space>last name. I called it ‘full_name’
->addAttributeToFilter(‘full_name’,array(‘like’=>’%’.$fullNameFilter.’%’)) //** Step 2: use the new attribute/column as a filter.