2

I have the following query in MySQL. I need to convert it for magento.

My query is,

SELECT latitude, longitude, SQRT(
    POW(69.1 * (latitude - [startlat]), 2) +
    POW(69.1 * ([startlng] - longitude) * COS(latitude / 57.3), 2)) AS distance
FROM TableName HAVING distance < 25 ORDER BY distance;

I know how to use addFieldToFilter() for a single column and multi-column. but this is little confusing me.

1 Answer 1

6

Give this a try:

$collection = Mage::getModel('model/alias_here')->getCollection();
$collection->addExpressionFieldToSelect(
    'distance',
    'SQRT(
     POW(69.1 * ({{latitude}} - [startlat]), 2) +
     POW(69.1 * ([startlng] - {{longitude}}) * COS({{latitude}} / 57.3), 2))',
    array('{{latitude}}' => 'latitude', 'longitude' => '{{longitude}}')
);
$collection->getSelect()->having('distance < 25');
$collection->setOrder('distance');

I don't understant what [startlat] and [startlng] are, but I assume they are some fixed values.

2
  • [startlat] and [startlng] both are start point of latitude and longitude Aug 22, 2015 at 4:05
  • $collection->addExpressionFieldToSelect( 'distance', 'SQRT( POW(69.1 * (latitude - 44), 2) + POW(69.1 * ((-74) - longitude) * COS(latitude / 57.3), 2))' ); $collection->getSelect()->having('distance < 25'); This one is working for me Aug 22, 2015 at 4:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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