Solving ExpressionAttributeToSelect Issues in Magento
Hello fellow developers! Today, we’re going to dive into an interesting challenge I faced recently while working on a Magento project. Let’s explore why adding addExpressionAttributeToSelect directly in the block doesn’t always work as expected.
The Initial Approach: Adding ExpressionAttributeToSelect in Block
I started by trying to add addExpressionAttributeToSelect within my block, specifically in the Layer class (Mage_Catalog_Model_Layer::prepareProductCollection). Here’s how I initially approached it:
$col = Mage::getModel('catalog/product')->getCollection()->addExpressionAttributeToSelect(
'latlngdistance',
"ROUND(
DEGREES(
ACOS(
SIN(RADIANS( $lat )) * SIN(RADIANS( {{lat}} ))
+ COS(RADIANS( $lat )) * COS(RADIANS( {{lat}} ))
* COS(RADIANS( $lng - {{lng}} ))
) * 60 * 1.1515
) * $mile2km
, 1)",
array('lat', 'lng')
);
$this->getSelect()->having('latlngdistance <= ?', $dst);
$col->getSelect()->limit(10);
$col->load();
This code seemed to work perfectly when run separately. However, when I added it within the block method, Magento mysteriously removed the addExpressionAttributeToSelect call entirely.
The Mysterious Error: Column Not Found
When I tried to use this modified collection in my block, I encountered a puzzling error:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'latlngdistance' in 'having clause'
This error suggested that the latlngdistance expression was being ignored. The code looked correct, so I was left wondering why Magento was stripping out my carefully crafted collection manipulation.
The Edit That Actually Matters
After some debugging and head-scratching, I realized that the issue wasn’t with the block itself, but rather with the collection’s count calculation method (_getSelectCountSql). By default, Magento uses this method to optimize queries for large collections. However, it can interfere with custom expressions added to the select.
To solve this, we need to rewrite the _getSelectCountSql method in our custom module’s collection class. This allows us to handle flat and EAV tables correctly while preserving our expression:
protected function _getSelectCountSql($select = null, $resetLeftJoins = true)
{
// Original count select (for reference)
$countSelect = parent::_getSelectCountSql($select, $resetLeftJoins);
if ($this->getDirectCurPage() == 1) {
// Your calculation logic here
// ...
}
return $countSelect;
}
The key is to make sure your expression is added directly to the original count select before it’s used for optimization. This ensures that Magento doesn’t strip out your custom expressions during the optimization process.
The Final Solution: Rewrite Your Collection
After much trial and error, I found that rewriting the collection class in my module was the most reliable solution:
protected function _getSelectCountSql($select = null, $resetLeftJoins = true)
{
// Original count select (for reference)
$countSelect = parent::_getSelectCountSql($select, $resetLeftJoins);
if ($this->getDirectCurPage() == 1) {
$mile2km = $this->mile2km;
$lat = $this->lat;
$lng = $this->lng;
$_col_lat = 'e.lat';
$_col_lng = 'e.lng';
// Handle flat and EAV tables
if (!$this->isEnabledFlat()) {
// ... (attribute handling code)
}
// Add your expression directly to the count select
$countSelect->columns('COUNT(DISTINCT e.entity_id) as count');
$countSelect->columns(array(
'latlngdistance' => new Zend_Db_Expr("ROUND(
DEGREES(
ACOS(
SIN(RADIANS( $lat )) * SIN(RADIANS( {$_col_lat} ))
+ COS(RADIANS( $lat )) * COS(RADIANS( {$_col_lat} ))
* COS(RADIANS( $lng - {$_col_lng} ))
) * 60 * 1.1515
) * $mile2km
, 1)"));
$countSelect->group('latlngdistance');
// Optimize the count select for large collections
$OldcountSelect = $countSelect;
$countSelect = $this->getConnection()->select();
$countSelect->from(array('a' => $OldcountSelect), array('SUM(a.count)'));
unset($OldcountSelect);
}
return $countSelect;
}
By rewriting the collection class and handling both flat and EAV tables, we ensure that our expression is added correctly to the count select. This approach preserves our custom logic while allowing Magento’s optimization mechanisms to function as intended.
Conclusion: Rewriting Collections for Custom Expressions
Adding addExpressionAttributeToSelect directly in a block can lead to unexpected behavior due to Magento stripping out the expression during collection optimization. To resolve this, we need to rewrite the collection class and handle the count query manually.
By rewriting the _getSelectCountSql method in our custom module’s collection class, we can add our custom expressions directly to the original count select before it’s optimized. This approach allows us to maintain control over our expression while still benefiting from Magento’s built-in optimizations for large collections.
Don’t forget to check out Laravel Company for more insights on Magento development and best practices!