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.

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 – Vignesh Bala Aug 22 '15 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 – Vignesh Bala Aug 22 '15 at 4:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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