I have created one magento collection like this to calculate distance from lat long.
$collection = Mage::getModel('module/module')->getCollection()->addFieldToFilter('status',1);
$collection->getSelect()->columns(array('distance' => new Zend_Db_Expr("( 6371 * acos( cos( radians(23.0130648) ) * cos( radians( latitude ) ) * cos( radians( longitude) - radians(72.4909026) ) + sin( radians(23.0130648) ) * sin( radians( latitude ) ) ) )")))
->having('distance <10')
->order('distance ' . Varien_Db_Select::SQL_ASC);
But I am getting error SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'having clause'.
When I print collection object Distance calculation is right. so what was the problem?
My problem is similar like this question Issue using "having" in Magento collection
I am using pagination on collection, if I remove pagination class then it works perfect.
But I am unable to solve with solution provided.
Here's my pagination code.
In _prepareLayout()
function I put this
$pager = $this->getLayout()->createBlock('page/html_pager', 'pager');
$pager->setAvailableLimit(array(5=>5,10=>10,20=>20,'all'=>'all'));
$pager->setCollection($this->getCollection());
$this->setChild('pager', $pager);
$this->getCollection()->load();
return $this;
add this function also in block file
public function getPagerHtml()
{
return $this->getChildHtml('pager');
}
and call this in phtml file as <?php echo $this->getPagerHtml(); ?>
Best Answer
I can try to use
addExpressionFieldToSelect
.You can find the method in
Mage_Core_Model_Resource_Db_Collection_Abstract
.In your case it should be something like this: (This is just an assumption, you might get some errors, but the idea is ok)
The
addExpressionFieldToSelect
works like this:the first parameter is the alias of the expression (virtual field name).
The second parameter is the expression. Replace the field names with placeholders wrapped arround
{{...}}
The third parameter is the placeholder correspondence (without
{{}}
). In your caselatitide
placeholder corresponds tolatitude
field so{{latitude}}
will be replaced withlatitude
. Same goes forlongitude
.[EDIT]
There is a problem when adding pagination to the
$collection
like thisHere is the backtrace of the issue. When the collection is loaded this is called
_renderLimit()
. The method looks like thisSo this calls
getCurPage()
(seeVarien_Data_Collection
class).getCurPage
has an additional verification to see if the page number is not outside the max range so it calculates the total number of pages ingetLastPageNumber()
.The problem here is that Magento resets the columns in the select for calculating the collection size. In
Varien_Data_Collection_Db::getSelectCountSql
there is this:By reseting the columns you end up with this sql
This is what generates the error.
I see 2 options here.
You override in your collection class the method
getSelectCountSql
and remove the column reset:You override the
getCurPage()
method to skip the range validation:[EDIT TO EDIT]
To avoid affecting the rest of the modules you can override the
getCurPage
method like this:Now when you want to use your
having
method just add this to your collection