Magento Sort Attribute by Decimal not Alphanumerically

Question

So I've Googled like crazy to try and find a solution to this problem that actually works properly but have come up empty handed.

When using the Sort By function on a category page to sort products by an attribute (capacity ,weight etc). Magento sorts like this because it thinks the number is a text string:

Product A: 10kg

Product B: 11kg

Product C: 15kg

Product D: 9kg

whereas it should sort like:

Product D: 9kg

Product A: 10kg

Product B: 11kg

Product C: 15kg

Looking around it seems like people suggest to change backend_type to decimal and frontend_input to price in the eav_attribute table for attributes that you'd like to sort numerically. However, not only does this not seem to actually work, but it changes the format of the number to have a dollar ($) symbol in front of it and because we display the actual attribute value on the product page, on top of using it to sort by, we doesn't work as a fix.

I'm trying to figure out exactly how the getSortOrder() method works but it looks like this functionality is pretty deeply embedded so I'm struggling to figure a workaround for this bug.

Any help is appreciated!

EDIT:

For anyone looking to solve this issue in the future, here's the fix I came up with:

You'll need to override the function _getProductCollection() in List.php that is stored in app/Code/Mage/core/catalog/block/product/list.php.
Copy the file to app/code/Mage/local/catalog/block/product/list.php so that you aren't editing core files.

Then below where it says:

$this->_productCollection = $layer->getProductCollection();

Put 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....

Now if you have Input Validation for Store Owner in the admin panel for the attribute set to Decimal Number or Integer Number: Then this code will reset the sort order on a product collection and then CAST it as a signed integer so that it sorts numerically rather than alphanumerically.

Hope that helps someone!

Answer 1

So I found a thread on this on their documentation, and apparently it's a known pain point for the product. The best solution I found was to override the ORDER BY for the query by calling a primitive method of the Collection class, here's the example they give:

$_productCollection = Mage::getModel('catalog/product')->getCollection();

$_productCollection->setOrder(array('cm_brand', 'name', 'cm_length'), 'asc');
$_productCollection->getSelect()->reset(Zend_Db_Select::ORDER);
$_productCollection->getSelect()->order(array('cm_brand ASC', 'name ASC', 'CAST(`cm_length` AS SIGNED) ASC'));

Based on your example with only one sorting column, I would think you could go with:

$_productCollection = Mage::getModel('catalog/product')->getCollection();
$_productCollection->setOrder('weight', 'asc');
$_productCollection->getSelect()->reset(Zend_Db_Select::ORDER);
$_productCollection->getSelect()->order('CAST(`weight` AS SIGNED) ASC'));

Answer 2

In response to Adam B's post.

My situation: I needed to sort products by a custom Magento (1.8.1.0) attribute with a numerical value. However, Magento was sorting like:

1 , 11 , 123 ,2 ,234, 3 (alphanumerical) instead of: 1, 2, 3, 11, 123, 234 (numerical)

I honestly don't get why this is not working out of the box but I got this working with the following adaption:

$this->_productCollection->getSelect()->order(new Zend_Db_Expr("CAST( ".$filterAttribute." AS SIGNED ) ".$filterAttributeDir));

Hope it's to any use for someone.