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)
$collection = Mage::getModel('module/module')->getCollection()->addFieldToFilter('status',1);
$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');
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 case latitide
placeholder corresponds to latitude
field so {{latitude}}
will be replaced with latitude
. Same goes for longitude
.
[EDIT]
There is a problem when adding pagination to the $collection
like this
$collection->setCurPage(1)->setPageSize(5);
Here is the backtrace of the issue.
When the collection is loaded this is called _renderLimit()
. The method looks like this
protected function _renderLimit()
{
if($this->_pageSize){
$this->_select->limitPage($this->getCurPage(), $this->_pageSize);
}
return $this;
}
So this calls getCurPage()
(see Varien_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 in getLastPageNumber()
.
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:
$countSelect->reset(Zend_Db_Select::COLUMNS);
By reseting the columns you end up with this sql
SELECT COUNT(*) FROM `table_name_here` AS `main_table` HAVING (distance < 10)
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:
public function getSelectCountSql()
{
$this->_renderFilters();
$countSelect = clone $this->getSelect();
$countSelect->reset(Zend_Db_Select::ORDER);
$countSelect->reset(Zend_Db_Select::LIMIT_COUNT);
$countSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
//$countSelect->reset(Zend_Db_Select::COLUMNS);//comment this line
$countSelect->columns('COUNT(*)');
return $countSelect;
}
You override the getCurPage()
method to skip the range validation:
public function getCurPage($displacement = 0){
if (!empty($this->_curPage)){
return $this->_curPage + $displacement;
}
return 1;
}
[EDIT TO EDIT]
To avoid affecting the rest of the modules you can override the getCurPage
method like this:
public function getCurPage($displacement = 0){
if (!$this->getDirectCurPage()){//if a specific flag is not set behave as default
return parent::getCurPage($displacement);
}
if (!empty($this->_curPage)){
return $this->_curPage + $displacement;
}
return 1;
}
Now when you want to use your having
method just add this to your collection
$collection->setDirectCurPage(1);
options' => array('Please Select', 'On Hold' => 'On Hold', 'Credit Hold' => 'Credit Hold', 'Open' => 'Open')
Why don't you just put each label as string? This worked fine for me.
Best Answer
Firstly - be careful with joining live tables on your sales_order_grid in the admin view. The reason is simple - any action or long-running query that your admins might be executing (for instance, sorting by a custom unindexed column with no other filters and 300k+ orders) may cause locks for your join table and prevent production orders.
Your column defines this as
type
ofoptions
but the option list you're providing is the entire invoice collection!!!Out of the box, invoice
state
is only 1 of 3 potential values:So you can handle it one of two ways - explicitly state the options:
Or, use something handy that Magento provides -- a static method for all invoice states in case they differ from version to version:
I prefer the latter as I am not a fan of typing out array options manually :)
Cheers.