0

I have created an own product-attribute "height" in magento-backend. The input type is "text" (because there was no similar type like "float"). To use this attribute as a filter I wrote a small extension. In this extension I filter the products with 

$this->_productCollection->addAttributeToFilter('height', array('lteq' => $height) );

When a products height is 69cm (69.00) and the filter is "<=80cm" (lteq 80.00) the filter works fine. But it doesn't work when the filter is "<=100cm" (lteq 100.00). This is because magento created the height-attribute as varchar and so the function addAttributeToFilter makes a string-compare. In string-compare "69" is greater than "100" because "6" is greater than "1".

Is there a way to tell addAttributeToFilter that it should compare the values as numbers instead of strings?

(I know the best way would be to delete the attribute and create a new one with type "float" via extension. But the shop is nearly finished and already contains products so I would like to avoid making such "big" changes in this state.)

Improve this question
2
  • Input type could be text but attribute's backend type should've been float/decimal. addAttributeToFilter merely translates the methods into equivalent SQL statements, so it's going to be a mySql comparison not PHP. If you are having clean data(properly validated) in that attribute, updating attribute's backend type should not cause any damage, although I'd try to avoid that too, but I see that's the only option left here.
    – Prateek
    Commented Oct 9, 2015 at 8:59
  • 1
    To fix this issue change attribute backend_type to decimal using script. it should work but before that,keep backup of your database
    – Minesh Patel
    Commented Oct 9, 2015 at 9:19

1 Answer 1

Reset to default
0

The comment of Prateek with the textpart

it's going to be a mySql comparison not PHP

guided me to a solution without changing the field-type. I've changed my code for Mage_Catalog_Block_Product_List in function _getProductCollection into:

...
if(isset($_GET['height']) && ''!=trim($_GET['height'])){

    // this line adds the attribute "height" to the query (without this line we couln't use the field in our own constraint) 
    $this->_productCollection->addAttributeToFilter('height', array('gteq' => 0) );     

    // we are in search (in search there is an additional height-table with the name at_height_default)
    if(false!==strpos($this->_productCollection->getSelect(), 'at_height_default')){
        $this->_productCollection->getSelect()->where(new Zend_Db_Expr(
            "CAST(IF(at_height.value_id > 0, at_height.value, at_height_default.value) AS DECIMAL(9,2)) <= ".(float) $height
        )); 
    }
    // we are in normal list-view (here the height-table has the name at_height)
    else{
        $this->_productCollection->getSelect()->where(new Zend_Db_Expr(
            "CAST(at_height.value AS DECIMAL(9,2)) <= ".(float) $height
        ));
    }
}
...

Of course I didn't changed the code in the core. I've overwritten the function in my extension. Because of the typecast the values are compared as numbers instead of strings. It's not the cleanest solution but ok for me at the moment.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.