Using modifyElementsQuery() to build a Radius Search? I am looking to build a radius search from my field type’s modifyElementsQuery() method.

I am storing lat/lng values in a separate table, and am trying to integrate the location search based on the following:

https://developers.google.com/maps/articles/phpsqlsearch_v3?csw=1#findnearsql

Here’s my current code. It throws an error saying "unknown column 'distance' in having clause".

public function modifyElementsQuery(DbCommand $query, $value)
{

    if($value !== null)
    {
        $settings = $this-&gt;getSettings();
        $this-&gt;plugin-&gt;pluginLog($value);

        $unitAdjustment = $settings-&gt;unit == 'mi' ? 3959 : 6371;
        $lat = 37;
        $lng = -122;
        $radius = $value;

        $query-&gt;addSelect('( '.$unitAdjustment.' * acos( cos( radians( '.$lat.' ) ) * cos( radians( fruit_geo.lat ) ) * cos( radians( fruit_geo.lng ) - radians( '.$lng.' ) ) + sin( radians( '.$lat.' ) ) * sin( radians( fruit_geo.lat ) ) ) ) as distance');
        $query-&gt;join('fruit_geo fruit_geo', 'fruit_geo.elementId = content.elementId');
        $query-&gt;having('distance &lt; '.$radius);


        // Here's the SQL statement that will find the closest 20 locations that are within a radius of 25 miles to the 37, -122 coordinate. 
        // It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, 
        // and then asks for only rows where the distance value is less than 25, orders the whole query by distance, 
        // and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371

        //SELECT id, ( $unitAdjustment * acos( cos( radians( $lat ) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians( $lng ) ) + sin( radians( $lat ) ) * sin( radians( lat ) ) ) ) AS distance FROM table HAVING distance &lt; $radius
    }

}


Template errors:

&lt;p&gt;CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'having clause'. The SQL statement executed was: SELECT `elements`.`id`
FROM `craft_elements` `elements`
JOIN `craft_elements_i18n` `elements_i18n` ON elements_i18n.elementId = elements.id
JOIN `craft_content` `content` ON content.elementId = elements.id
JOIN `craft_fruit_geo` `fruit_geo` ON fruit_geo.elementId = content.elementId
JOIN `craft_entries` `entries` ON entries.id = elements.id
JOIN `craft_sections` `sections` ON sections.id = entries.sectionId
LEFT JOIN `craft_structures` `structures` ON structures.id = sections.structureId
LEFT JOIN `craft_structureelements` `structureelements` ON (structureelements.structureId = structures.id) AND (structureelements.elementId = entries.id)
WHERE (((((((elements_i18n.locale = :locale) AND (content.locale = :locale)) AND (elements.archived = 0)) AND ((elements.enabled = 1) AND (elements_i18n.enabled = 1) AND (entries.postDate &lt;= '2015-02-06 11:57:46') AND ((entries.expiryDate is null) OR (entries.expiryDate &gt; '2015-02-06 11:57:46')))) AND (elements_i18n.enabled = 1)) AND (content.field_date&gt;=:contentfield_date1)) AND (sections.handle=:sectionshandle1)) AND (`elements`.`id` IN ('5747', '5823', '8529', '8865', '8913', '8972', '8973', '8978', '8979', '10228', '10370', '10912', '11780', '13366', '14673', '14675', '14759', '14764', '14770', '20516', '20682', '20683', '21042', '21043', '21143', '22247', '22570', '22814', '22817', '22827', '22830', '22834', '22835', '22836', '22844', '22855', '22859', '22860', '22867', '22899', '22909', '22916', '22945', '22970'))
GROUP BY `elements`.`id`
HAVING distance &lt; 50
ORDER BY `field_date` ASC. Bound with :locale='en_gb', :contentfield_date1='2015-02-06 00:00:00', :sectionshandle1='events' (/var/www/vhosts/findarace.com/beta.findarace.com/craft/app/framework/db/CDbCommand.php:543)&lt;/p&gt;&lt;pre&gt;#0 /var/www/vhosts/findarace.com/beta.findarace.com/craft/app/framework/db/CDbCommand.php(396): CDbCommand-&gt;queryInternal('fetchAll', Array, Array)
So it turned out that the distance column error was sending us down the wrong path.

The issue was caused by 2 things:

1) As the original Craft entries query was using ids() any select string added via addSelect was being disregarded, the answer here was to move this logic into the having clause.

2) The second issue that was preventing the having clause from working was a missing groupBy clause.

The modifyRadiusQuery code in full:

private function modifyRadiusQuery(DbCommand $query, $value)
{
    $settings = $this-&gt;getSettings();
    $measurementUnit = $settings-&gt;unit == 'mi' ? 3959 : 6371;

    $geo = craft()-&gt;logic-&gt;stringToGeo($value['location']);

    if($geo)
    {
        $haversine = $this-&gt;haversine($geo['lat'], $geo['lng']);

        // ids()
        $query-&gt;join(Logic_GeoRecord::TABLE_NAME.' geo', 'elements.id = geo.elementId');
        $query-&gt;having($haversine.' &lt; '.$value['radius']);
        $query-&gt;group('geo.lat, geo.lng');

        // find()
        // $haversine = $this-&gt;haversine($geo['lat'], $geo['lng']);
        // $query-&gt;addSelect($haversine.' AS distance');    
        // $query-&gt;join(Logic_GeoRecord::TABLE_NAME.' geo', 'elements.id = geo.elementId');
        // $query-&gt;having('distance &lt; '.$value['radius']);
        // $query-&gt;group('geo.lat, geo.lng');
    }
}


The DBCommand query that was fixed:

        $query-&gt;join(Logic_GeoRecord::TABLE_NAME.' geo', 'elements.id = geo.elementId');
        $query-&gt;having($haversine.' &lt; '.$value['radius']);
        $query-&gt;group('geo.lat, geo.lng');