Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

lower case in attribute filter in collection
 
heaven dweller
Jr. Member
 
Total Posts:  18
Joined:  2009-11-09
 

Hi,
I want to use lower case function for name in product collection. For example

Mage::getModel('catalog/product')->getCollection()->addAttributeExpressionToFilter(new Zend_Db_Expr ('"LCASE(TRIM(name))'),'test');

But this is not working. Can anyone help ?

 
Magento Community Magento Community
Magento Community
Magento Community
 
kiatng
Enthusiast
 
Total Posts:  875
Joined:  2008-09-03
Kuala Lumpur, Malaysia
 

Why not do it PHP

Mage::getModel('catalog/product')->getCollection()->addAttributeExpressionToFilter(new Zend_Db_Expr (strtolower(trim($name)),'test');
 
Magento Community Magento Community
Magento Community
Magento Community
 
heaven dweller
Jr. Member
 
Total Posts:  18
Joined:  2009-11-09
 

Actually I need to do it in query as filter for example

select from product where lcase(trim(name)) = \'testname\'
 
Magento Community Magento Community
Magento Community
Magento Community
 
kiatng
Enthusiast
 
Total Posts:  875
Joined:  2008-09-03
Kuala Lumpur, Malaysia
 

MySQL is case insensitive. For example I have extra spaces and wrong case in the following select statement which works

SELECT FROM `catalog_product_entity_varcharwhere     VaLUe_iD 5
 
Magento Community Magento Community
Magento Community
Magento Community
 
heaven dweller
Jr. Member
 
Total Posts:  18
Joined:  2009-11-09
 

kiatng, mysql is case insensitive but spaces are not trimmed

 
Magento Community Magento Community
Magento Community
Magento Community
 
kiatng
Enthusiast
 
Total Posts:  875
Joined:  2008-09-03
Kuala Lumpur, Malaysia
 

Try this:

Mage::getModel('catalog/product')->getCollection()->addAttributeExpressionToFilter(new Zend_Db_Expr ('LCASE(TRIM(name))'),'test');

You have an extra “ in your original statement.

 
Magento Community Magento Community
Magento Community
Magento Community
 
heaven dweller
Jr. Member
 
Total Posts:  18
Joined:  2009-11-09
 

I think addExpressionAttributeToFilter is not available

 
Magento Community Magento Community
Magento Community
Magento Community
 
ilm_abhishek
Jr. Member
 
Total Posts:  4
Joined:  2012-05-03
 

hi

try this

addFieldToFilter($field$condition=null)

or

$collection->addAttributeToFilter('field_name', array(
    
'in' => array(123),
    ));

Thanks,
ilm_abhishek

 
Magento Community Magento Community
Magento Community
Magento Community
 
heaven dweller
Jr. Member
 
Total Posts:  18
Joined:  2009-11-09
 

I need to trim column value also

 
Magento Community Magento Community
Magento Community
Magento Community
 
ilm_abhishek
Jr. Member
 
Total Posts:  4
Joined:  2012-05-03
 

$write = Mage::getSingleton(’core/resource’)->getConnection(’core_write’);

// now $write is an instance of Zend_Db_Adapter_Abstract
$readresult=$write->query("SELECT *
FROM `pepitashop_catalog_product_super_attribute_pricing` , `pepitashop_catalog_product_entity`,`pepitashop_catalog_product_super_attribute`
WHERE
`pepitashop_catalog_product_super_attribute`.product_super_attribute_id = `pepitashop_catalog_product_super_attribute_pricing`.product_super_attribute_id
AND
pepitashop_catalog_product_super_attribute.product_id = `pepitashop_catalog_product_entity`.entity_id
AND pepitashop_catalog_product_super_attribute.product_id = ‘“.$_id.”’
ORDER BY `pepitashop_catalog_product_super_attribute_pricing`.pricing_value DESC
LIMIT 0 , 30 “);

while ($row = $readresult->fetch() ) {
$categoryIds[]=$row[’id’];
}

 
Magento Community Magento Community
Magento Community
Magento Community
 
heaven dweller
Jr. Member
 
Total Posts:  18
Joined:  2009-11-09
 

I have come up with this solution :

$collection1 Mage::getModel('catalog/product')->getCollection();
$collection1->getSelect()->columns(array('name'=>new Zend_Db_Expr('lcase(trim(name))')));

$collection2 Mage::getModel('catalog/product')->getCollection();
    
$collection2->getSelect()->joinInner(array('e1' => $collection1->getSelectSql()), 'e1.entity_id=e.entity_id');    
$collection2->getSelect()->where("e1.name = 'test'");
 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top