Demac Media Demac Media Brand Demac Media Brand



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

Timer Icon 1 Min

Development, Magento Commerce, Magento Tutorials

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.

Subscribe to the Blog

Stay updated with 20,000+ eCommerce leaders in our community

Sign Up

Let’s talk about your 10X return

Icon/Social/Phone Contact us for pricing information