Sorting Magento Products by Attribute: Decimal vs Alphanumeric
The Challenge
Sorting products by attributes is a fundamental feature in any e-commerce platform, including Magento. However, when it comes to sorting based on numerical values like weight or capacity, Magento has a peculiar default behavior that can be frustrating for both customers and administrators alike.
By default, Magento treats numerical attributes as text strings when performing the sort operation. This means it sorts them alphanumerically instead of numerically, which is counterintuitive and inconvenient. Let me illustrate this with an example:
The Current Behavior
Consider you have a category containing products with different capacities:
- Product A: 10kg
- Product B: 11kg
- Product C: 15kg
- Product D: 9kg
When you use the Sort By function to sort by capacity, Magento currently sorts like this:
Product A: 10kg
Product B: 11kg
Product C: 15kg
Product D: 9kg
Notice how “9kg” appears last even though it should be first in a numerical sort.
The Common Misconceptions
Many developers, including myself initially, try to solve this issue by changing the attribute settings in the database:
- They update the
backend_typeto decimal. - They set
frontend_inputto price. - They hope that Magento will now sort the products correctly.
However, this approach doesn’t work as expected. Not only does it not resolve the sorting issue, but it also changes the display format of the attribute value on your product pages to include a dollar ($) symbol in front of the number.
The Correct Solution
After much investigation and experimentation, I found a clean way to force Magento to sort decimal attributes numerically rather than alphabetically. Here’s how you can do it:
Step 1: Override the _getProductCollection method
You need to overwrite the _getProductCollection function in List.php, which is located at app/Code/Mage/core/catalog/block/product/list.php.
First, copy this file to app/code/Mage/local/catalog/block/product/list.php. This ensures you’re not editing any core files.
Step 2: Add the Custom Sort Logic
Find where it says $this->getProductCollection(); in your overwritten List.php file. Below this line, add the following code:
// Start of Code to force Magento to numerically sort decimal attributes rather than alphabetically
$filterAttribute = $this->getRequest()->getParam('order');
$filterAttributeDir = $this->getRequest()->getParam('dir');
$attributeType = Mage::getModel('eav/entity_attribute')->loadByCode('catalog_product', $filterAttribute)->getFrontendClass();
// If a Sort By option is selected on category page and attribute has frontend_class = validate-number or validate-digits
// then CAST the attribute values as signed integers
if (isset($filterAttribute) && ($attributeType == 'validate-digits' || $attributeType == 'validate-number')) {
$this->_productCollection->getSelect()->reset(Zend_Db_Select::ORDER);
$this->_productCollection->getSelect()->order('CAST(`' . $filterAttribute . '` AS SIGNED) ' . $filterAttributeDir . "'");
}
// End of code to force Magento to numerically sort....
Step 3: Set the Attribute Type in Admin
For this solution to work, ensure that your attributes have Input Validation for Store Owner set to Decimal Number or Integer Number in the admin panel:
[Image showing attribute type setting]
Conclusion
Sorting products by decimal attributes is a common need but a tricky issue in Magento. By following these steps and implementing the custom sort logic, you can now force Magento to sort your products numerically rather than alphabetically.
Remember, always test changes in a staging environment before deploying them to a live store. If you’re new to Magento development or unsure about any part of this process, consider reaching out to professional Magento support services like those offered at Laravel Company.
This solution should provide the numerical sorting you need while keeping your product display clean and user-friendly. Happy coding!