Filtering MySQL-Computed Text Columns in Magento Admin Grid
Magento’s admin grid provides powerful filtering capabilities for data management. However, when dealing with columns that contain computed values based on other fields, the default filtering behavior may not meet your needs. In this blog post, we’ll explore how to modify a text column with computed “Yes” and “No” values so it can be filtered by entering “yes”.
Understanding the Issue
Let’s assume you have an admin grid column named ‘refunded’ that displays whether an item has been refunded or not. The computation is based on the ‘qty_refunded’ field:
'setCollection()': array(
// Other configurations...
'refunded' => new Zend_Db_Expr("IF(qty_refunded > 0, 'Yes', 'No')"),
),
And your column configuration looks like this:
$this->addColumnAfter('refunded', array(
'header' => Mage::helper('helper')->__('Refunded'),
'index' => 'refunded',
'type' => 'text',
), 'qty');
By default, if an admin user types “yes” in the filter box, the filter won’t work because it’s looking for a match against the computed field directly (‘refunded’), not the underlying value that produced the “Yes”.
Solving the Problem
To enable filtering by entering “yes”, you need to create a new column that stores the actual data you want to filter. Let’s call this new column ‘refunded_status’.
First, modify your collection setup:
'refunded_status' => new Zend_Db_Expr("IF(qty_refunded > 0, '1', '0')"),
Now, update the admin grid column configuration to use this new field for the filter text:
$this->addColumnAfter('refunded_status', array(
'header' => Mage::helper('helper')->__('Refunded'),
'index' => 'refunded_status',
'type' => 'options',
'options' => array(
1 => 'Yes',
0 => 'No',
),
), 'qty');
In the updated configuration:
- We’re now using a new column named
refunded_status. - The index is set to this new field.
- For the type, we use
'options'which allows us to define a filterable list of options. - We provide an array of options that match our computed values.
Advanced Filtering
To enable advanced filtering where admins can search for both “Yes” or “No”, you can modify the options array:
'options' => array(
1 => 'Yes',
0 => 'No',
'' => Mage::helper('helper')->__('All'),
),
Here, we’ve added an empty string as an option. When selected, it will show all records regardless of whether they’re refunded or not.
Conclusion
Filtering computed text columns in Magento admin grid requires a bit of extra setup. By creating a new column that stores the underlying data for filtering and modifying your column configuration appropriately, you can now enable admins to filter using values like “yes”.
Remember, when working with complex queries and computations in the admin grid, always ensure your database is optimized to handle potential performance impacts.
For more advanced Magento development techniques and best practices, be sure to check out our Magento Development Guides at Laravel Company.