7

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(); ?>

12

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);
  • This method still not working error continue. – Mufaddal Aug 2 '13 at 8:09
  • What happens if you remove the having statement and loop through the collection? Do you see a value for distance in the item data? – Marius Aug 2 '13 at 8:12
  • yes I can see a value of distance item if I remove having cluase. – Mufaddal Aug 2 '13 at 8:16
  • This is strange. I tested as a proof of concept on a core collection and it works great. $collection = Mage::getModel('cms/block')->getCollection() ->addExpressionFieldToSelect('some_total', 'SUM({{is_active}})', array('is_active'=>'is_active')); $collection->getSelect()->having('some_total > 1')->order('some_total ASC'); Maybe is something wrong with your module. – Marius Aug 2 '13 at 8:29
  • One thing, I am using pagination on collection so it creates issue if I remove pagination then your methods work correctly – Mufaddal Aug 2 '13 at 8:33
2

Try using this instead:

$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 ) ) ) )")))
            ->addAttributeHaving('distance <10')
            ->addAttributeToSort('distance', Varien_Db_Select::SQL_ASC);
  • its not working it returns null object – Mufaddal Aug 2 '13 at 7:21
1

You could try where() instead of having() as you don't use any JOIN statements:

$collection->getSelect()->where('distance > ?', 10);
  • No it still return error Unknown column 'distance' in 'where clause – Mufaddal Aug 2 '13 at 10:23

protected by Community Apr 27 '15 at 5:06

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.