using addExpressionFieldToSelect for EAV model

Question

I am trying to get records of nearby places within some distance from given latitude and longitude from an EAV model in which latitude and longitude are attributes.

I am using below code but getting 500 error when addExpressionFieldToSelect is used to select distance using expression.

$collection = Mage::getModel('test/test')->getCollection()->addAttributeToSelect(array('latitude','longitude'));
 $collection->addExpressionFieldToSelect('distance', '( 6371 * acos( cos( radians(23.0130648) ) * cos( radians( {{latitude}}) ) * cos( radians( {{longitude}}) - radians(72.4909026) ) + sin( radians(23.0130648) ) * sin( radians( {{latitude}}) ) ) )', array('latitude'=>'latitude', 'longitude'=>'longitude'));
 $collection->getSelect()->having('distance > 10');
 foreach($collection as $c)
 {
 echo $c->getLatitude();echo "<br/>";
 echo $c->getLongitude();
 }

• Richard: Then check your php log, which will tell you why you got an internal server error. It's probably an error in the sql being generated in your query, if you cast the select to a string you can grab what it is you're generating (e.g. Mage::log((string)$collection->getSelect()); before you try and iterate it
• HungryDB: Ohh i am getting call to undefined function ::addExpressionFieldToSelect(). How to add it in my custom module?

Answer 1

Why are you trying to call a function that doesn't exist? Why do/did you expect Mage::getModel('test/test')->getCollection() to return something that would have that function? The collection would have to inherit from Mage_Core_Model_Resource_Db_Collection_Abstract to get that in magento.

This is the code of the function

 /**
 * Add attribute expression (SUM, COUNT, etc)
 * Example: ('sub_total', 'SUM({{attribute}})', 'revenue')
 * Example: ('sub_total', 'SUM({{revenue}})', 'revenue')
 * For some functions like SUM use groupByAttribute.
 *
 * @param string $alias
 * @param string $expression
 * @param array $fields
 * @return Mage_Core_Model_Resource_Db_Collection_Abstract
 */
 public function addExpressionFieldToSelect($alias, $expression, $fields)
 {
 // validate alias
 if (!is_array($fields)) {
 $fields = array($fields=>$fields);
 }
 $fullExpression = $expression;
 foreach ($fields as $fieldKey=>$fieldItem) {
 $fullExpression = str_replace('{{' . $fieldKey . '}}', $fieldItem, $fullExpression);
 }
 $this->getSelect()->columns(array($alias=>$fullExpression));
 return $this;
 }

you can see that it essentially just calls columns on the Zend_Db_Select object. You can just do the same,

$collection->getSelect()->columns(array('distance'=>'( 6371 * acos( cos( radians(23.0130648) ) * cos( radians( latitude) ) * cos( radians( longitude) - radians(72.4909026) ) + sin( radians(23.0130648) ) * sin( radians( latitude) ) ) )'));

• HungryDB: So when i call columns on select object. i get following query - SELECT e.*, ( 6371 * acos( cos( radians(23.0130648) ) * cos( radians(latitude) ) * cos( radians(longitude) - radians(72.4909026) ) + sin( radians(23.0130648) ) * sin( radians(latitude) ) ) ) AS distance FROM main_entity_table AS e WHERE (e.entity_type_id = '9') HAVING (distance > 10) But, here latitude and longitude are attributes. so it shows an error of unknown field. I think i need to use joinAttribute here. What changes i should make now?
• Richard: No, it sounds like the model test/test and that collection are not set up properly at all. It doesn't have it's table name defined properly, does the table with those fields even exist? You might want to look at github.com/tzyganu/UMC1.9 which can do all the boilerplate for creating a new module for you.