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

For default sort order:

        // Start of Code to force Magento to numerically sort decimal attributes rather than alphabetically
        $filterAttribute = Mage::getBlockSingleton('catalog/product_list_toolbar')->getCurrentOrder();
        //$filterAttribute = $this->getRequest()->getParam('order');
        $filterAttributeDir = Mage::getBlockSingleton('catalog/product_list_toolbar')->getCurrentDirection();
        //$filterAttributeDir = $this->getRequest()->getParam('dir');

Answer 2

Sorry, I'm a little late to the discussion. As I'm adverse to implementing code solutions when it isn't strictly necessary, I tried to think of an easier solution. I came up with the following.

Example problem sorting Original sorting order: 10.0", 10.5", 14.0", 8.0", 8.5"

Given that the list of numbers is sorted alphanumerically, I deduced that adding offsetting space characters (" ") before the 8s in my example above should result in the correct ordering. It did. This was the result.

Example correct sorting New sorting order: 8.0", 8.5", 10.0", 10.5", 14.0"

In the OP, Adam might have simple been able to replace "9kg" with " 9kg".

By extension, if the numbers in question range from ones values to hundreds values, the ones values would have 2 leading spaces, the tens values would have 1 leading space, etc.

Answer 3

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 4

Regarding the "Attribute with Numerical Value" situation above. I had the same problem and what I did was adding ceros "0" in front of my values.

For ex, my values were: 208, 209, 355, 1152 and 1153.

I added ceros: 00208, 00209, 00355, 01152 and 01153.

It works now!

This post also helped me:

http://blog.adin.pro/2014-04-30/magento-custom-sort-on-grid-sort-increment_id-by-numeric-not-alpha/

Hope this help!

Answer 5

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.

Answer 6

Can you tell us the 'Catalog Input Type for Store Owner'? Eg is it text or drop down? I mean, do you have a 'position' column in the Magento Admin (Catalog->Attributes->Manage attributes->Manage Label / Options->Manage Options (values of your attribute)). If your attribute is text, is the actual value the string '9kg' or is it '9' with 'kg' added later?

Anyway, if you can set a position in the attribute editor then I think getSortOrder() will help you (confirm this and someone can post an answer about getSortOrder()).

If you cannot set a position (because the values are not pre-determined) then I think you will need to create the product sort order yourself* by perhaps stripping out the letters from the attribute values with a preg_replace() or str_replace(), sorting on the result of that and then passing the new sorted product array into the display loop - see 'rough pseudo code' in this answer.

*because I don't think any built-in generic sorting functions can tell the string '9kg' is less than teh string '11kg' or '3V' is less than '18V'

**EDIT following comment:

Ah, yes, and I see Magento does not have a numeric input type for Catalog Input Type for Store Owner. I think you should code your own sorting in the .phtml or the associated block php class (or if you know the weights in advance make it a dropdown eg 1,2,3,4... or 1.1, 1.2, 1.3...4.1, 4.2, 4.3...19.9) then you can tell Magento the position.

You might get away with the Date input type or even the Fixed product tax input type but I reckon that is asking for trouble (and a lot of testing).

Plan B might be to add some XML that defines a new numeric input type for Catalog Input Type for Store Owner but I'd leave that to the core Magento developers - maybe they have a good reason for storing attribute values as strings.

If you are using the system attribute Weight then that is fixed as input type text and I think you have no choice other than coding your own sort.

I just found

//file: app/code/core/Mage/Core/Model/Locale.php
//class: Mage_Core_Model_Locale
//...
     /*
     * @param string|float|int $value
     * @return float|null
     */
    public function getNumber($value){
//...

Which could be useful. I don't think it will take you long to code your own sort.