Adding a filter on an "addExpressionFieldToSelect" dynamic fields

[janizol]

Hi, I am doing a distance calculation based on latitude and longitude and use the addExpressionFieldToSelect to add a distance dynamic field in calling my collection from my custom modules factory. When I do a addFieldToFilter on the dynamic field distance I get an error stating that " Column not found: 1054 Unknown column 'distance' in 'where clause'" this is obviously due to the distance field not being a column in the database but a dynamic one I am creating for my calculation. My problem is that I need to get results based on the distance field. I cannot seem to find any reference to a possible method to used to filter by that dynamic field. Any help will do. getCollection call below. Thanks

 $stores = $this->_storesFactory->create()->getCollection()->addFieldToSelect('*')->addExpressionFieldToSelect('distance', '((ACOS(SIN('.$centerPointLat.' * PI() / 180) * SIN({{latitude}} * PI() / 180) + COS('.$centerPointLat.' * PI() / 180) * COS({{latitude}} * PI() / 180) * COS(('.$centerPointLong.' - {{longitude}}) * PI() / 180)) * 180 / PI()) * 60 * 1.1515)', array('latitude'=>'latitude', 'longitude'=>'longitude'))->addFieldToFilter('distance',array('lteq'=>20))->getData();
        return $stores;

[Suman Kar · Accepted solution]

Please make sure your collection is extending 

\Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection

Check this post 

https://magento.stackexchange.com/questions/6305/how-to-add-dynamic-field-in-magento-collection

$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 latitideplaceholder corresponds to latitude field so {{latitude}} will be replaced with latitude. Same goes for longitude.

So in your case probably following need to be changed with having

->addFieldToFilter('distance',array('lteq'=>20))