3

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->getSettings();
        $this->plugin->pluginLog($value);

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

        $query->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->join('fruit_geo fruit_geo', 'fruit_geo.elementId = content.elementId');
        $query->having('distance < '.$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 < $radius
    }

}

Template errors:

<p>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 <= '2015-02-06 11:57:46') AND ((entries.expiryDate is null) OR (entries.expiryDate > '2015-02-06 11:57:46')))) AND (elements_i18n.enabled = 1)) AND (content.field_date>=: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 < 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)</p><pre>#0 /var/www/vhosts/findarace.com/beta.findarace.com/craft/app/framework/db/CDbCommand.php(396): CDbCommand->queryInternal('fetchAll', Array, Array)
  • Not sure if this helps, but the Smart Map plugin was originally designed specifically to perform a radius search. – Lindsey D Feb 4 '15 at 20:19
  • Thanks Lindsey, but this needs to work as a part of craft query and we are using squareMaps with the mapbox api. Things appear to be getting stuck at the addSelect and the reference to distance in the HAVING clause?? – Sam Hibberd Feb 6 '15 at 7:24
  • What do you mean by "getting stuck"? That addSelect looks pretty solid... Is it returning the correct results, but not ordering them properly? – Lindsey D Feb 6 '15 at 7:40
  • It throws an error saying 'unknown column distance is having clause. – Sam Hibberd Feb 6 '15 at 7:43
  • My first instinct is to say triple-check the number of parenthesis you've got... but I just checked a minute ago and they looked fine. – Lindsey D Feb 6 '15 at 7:46
5

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->getSettings();
    $measurementUnit = $settings->unit == 'mi' ? 3959 : 6371;

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

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

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

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

The DBCommand query that was fixed:

        $query->join(Logic_GeoRecord::TABLE_NAME.' geo', 'elements.id = geo.elementId');
        $query->having($haversine.' < '.$value['radius']);
        $query->group('geo.lat, geo.lng');
| improve this answer | |
2

If I recall correctly (I battled this about a year ago)...

What you are calling "the query" is actually a sub-query. Craft is compiling a much larger query to get all the data it needs in a single shot. The problem is, as far as the parent query is concerned, distance is not a real column. Your distance specifications are confined to the child query, and never make it out to the larger query where it needs to be.

Anyway, my workaround was to convert the haversine formula into PHP, and process it on that side. It wasn't the prettiest solution, but it's been working well for about a year now.

| improve this answer | |
  • It’s actually no longer a sub query - that changed in Craft 2.0. – Brandon Kelly Feb 6 '15 at 11:08
  • @BrandonKelly if its no longer a sub query, do you have any idea whats causing the 'unknown column distance' issue? – Sam Hibberd Feb 6 '15 at 14:33
  • @BrandonKelly Well that changes everything! Any clue what may be causing the problem then? – Lindsey D Feb 6 '15 at 17:04
  • @LindseyD Actually ended up being somewhat related to it not being a sub-query - since it’s part of the main query, anything that was added by addSelect() was getting discarded because he was calling ids() instead of find(), hence the MySQL error. – Brandon Kelly Feb 18 '15 at 14:23
2

Here is a plugin that I'm using to geocode entries, and search based on closest within a range of (lat, lang) coordinates. In this case. it's storing the lat/lang fields on the entry itself, but you should be able to adapt it for your needs.

In the services file:

static private $url = "http://maps.google.com/maps/api/geocode/json?sensor=false&address=";

public function findClosest($address, $range, $section, $limit = 12) {

    $loc        = self::getLocation($address);
    $lat        = $loc['lat'];
    $lng        = $loc['lng'];
    $section    = $section;
    $range      = $range;
    $limit      = $limit;

    // define query
    $criteria = craft()->elements->getCriteria(ElementType::Entry);

    $query = craft()->elements->buildElementsQuery($criteria);
    $query->select(array('elements.id AS id', '( 3959 * acos( cos( radians(' . $lat . ') ) * cos( radians( field_lat ) ) * cos( radians( field_lng ) - radians(' . $lng . ') ) + sin( radians(' . $lat . ') ) * sin(radians( field_lat )) ) ) AS distance'));
    $query->andWhere('sections.handle = \''. $section.'\'');
    $query->having('distance < ' . $range);
    $query->order('distance');
    $query->limit($limit);

    $results = $query->queryAll();

    $locations = '';
    foreach ($results as $result)
    {
        $locations[] = array(
            'id'        => $result['id'],
            'distance'  => $result['distance'],
            'entry'     => craft()->entries->getEntryById($result['id'])
        );
    }
    return $locations;
}

public function geocodeEntry($entry)
{   
    $address = $entry->address.','.$entry->city.','.$entry->state.','.$entry->zip.','.$entry->country;

    $loc = self::getLocation($address);
    if ($loc)
    {
        $entry->getContent()->lat   = $loc['lat'];
        $entry->getContent()->lng   = $loc['lng'];
    }
}

static public function getLocation($address)
{
    $url = self::$url.urlencode($address);
    $resp_json = self::curl_file_get_contents($url);
    $resp = json_decode($resp_json, true);

    if ($resp['status'] == 'OK')
    {
        return $resp['results'][0]['geometry']['location'];
    }
    else
    {
        return false;
    }
}

static private function curl_file_get_contents($url)
{
    $c = curl_init();
    curl_setopt($c, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($c, CURLOPT_URL, $url);
    $contents = curl_exec($c);
    curl_close($c);

    if ($contents) 
    {
        return $contents;
    }
    else
    { 
        return false;
    }
}

In the variables file:

public function closest($address)
{
    return craft()->logic->findClosest($address, 50, 'SectionName', 9);
}

The init function within the plugin's main file:

public function init()
{
    parent::init();

    craft()->on('entries.beforeSaveEntry', function(Event $event) 
    {
        $entry = $event->params['entry'];
        if ($entry->section->handle == 'suppliers' || $entry->section->handle == 'retailers') 
        {
            craft()->myPlugin->geocodeEntry($entry);
        }
    });
}
| improve this answer | |
  • Thanks Douglas, but this needs to work as a part of craft query. – Sam Hibberd Feb 6 '15 at 7:26
  • I'm confused. What do you consider to be a 'craft' query? The haversine sql is just there for a reference. – Douglas McDonald Feb 6 '15 at 10:33
  • Sorry probably not the best terminology to use. Currently my solution needs to integrate with an existing entries query, there are a whole host of param's as a part of an advanced search from the front end. If its makes sense to build my own entries query I would be happy to do so, although would need to ensure that craft still gets all the stuff it needs and that it is future proof etc etc??? – Sam Hibberd Feb 6 '15 at 10:38
  • Ah, I see what you are doing now – looks like you are trying to modify an existing DBCommand query that is initiated elsewhere in a way that's portable. I wasn't looking at the method parameters. – Douglas McDonald Feb 6 '15 at 11:30

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.