Magento – Magento 2 Custom Module Delete Issue [SOLVED]

I am going to actually answer my own question here. I know, tacky, but I stumbled upon the answer when looking much closer at the actual stack trace. The collection is loading fine however, the failure comes a little later in execution when we try to get the collection count in Varien_Data_Collection_Db::getSelectCountSql(). The SQL that is produced from this is:

SELECT COUNT(*) FROM sales_flat_order AS main_table LEFT JOIN sales_flat_order_payment AS sfop ON main_table.entity_id = sfop.parent_id WHERE (main_table.state in ('payment_review')) AND (main_table.sd_order_type != 7) AND (sfop.method != 'giftcard') AND (main_table.created_at > '2013-04-07') GROUP BY main_table.entity_id HAVING (DIF_AU <> 0)

You will notice that the HAVING statement is attached yet we have no definition for the DIF_AU column any longer. It appears as though I will need to extend a custom getSelectCountSql() in my collection class to get the right record count.

I have created an extended getSelectCountSql() in the custom collection class that adds back in the missing column required for the having statement.

public function getSelectCountSql()
  {
    $countSelect = parent::getSelectCountSql();
    $countSelect->columns('grand_total - sum(sfop.amount_authorized) AS DIF_AU');
    $countSelect->reset(Zend_Db_Select::GROUP);
    return $countSelect;
  }

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.

1. 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;
   }
   

2. 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);