Jump to:

3460 Posts in 1064 Topics by 739 members

Data Model Questions

SilverStripe Forums » Data Model Questions » [SOLVED] DataObject Search by Distance

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

Page: 1
Go to End
Author Topic: 903 Views
  • joern
    Avatar
    Community Member
    28 Posts

    [SOLVED] DataObject Search by Distance Link to this post

    Hi,
    i'm working on a Google-Maps based Catalog and every Entry has a Location (Lat, Lng) via has_one-Relation.
    i found this example http://code.google.com/intl/en-US/apis/maps/articles/phpsqlsearch_v3.html#findnearsql but i don't know how to implement the query into silverstripes sqlQuery-Object.
    currently i use a subclass of the AdvancedSearchForm. It would be good to maintain that, because I need to search in other fields and this is still working.

    I hope you can help me.
    Jörn

  • joern
    Avatar
    Community Member
    28 Posts

    Re: [SOLVED] DataObject Search by Distance Link to this post

    I figure out how it works! here is my code:

    class FilterForm extends AdvancedSearchForm {

       function getQuery($searchParams, $sort = false, $limit = false, $existingQuery = null) {
          …
          
          // check if location fields are set and modify query
          if (isset($searchParams['Location__Lat']) || isset($searchParams['Location__Lng'])) {
             $query = $this->extendLocationSQL($searchParams, $query, $context);
          }
          
          foreach ($searchParamArray as $key => $data) {
             $key = str_replace('__', '.', $key);
             
             if ($filter = $context->getFilter($key)) {
             
                if (isset($data['filter'])) {
                   // remove old
                   $context->removeFilterByName($key);

                   // create new
                   $filter = new $data['filter']($key);
                   $context->addFilter($filter);
                }
                
                $filter->setModel($this->stat('classToSearch'));
                $filter->setValue($data['value']);
                
                if (!$filter->isEmpty()) {
                   $filter->apply($query);
                }
             }
          }
          
          $query->connective = $context->connective;
          $query->distinct = true;

          $model->extend('augmentSQL', $query);
          
          return $query;
       }

       /**
        * modify the current search-query to find entries by distance
        *
        */
       private function extendLocationSQL($searchParams, $query, $context) {
          
          if (isset($searchParams['Location__Distance'])) {
             
             $lat = $searchParams['Location__Lat'];
             $lat = isset($lat['value']) ? (float) $lat['value'] : false;
             
             $lng = $searchParams['Location__Lng'];
             $lng = isset($lng['value']) ? (float) $lng['value'] : false;
             
             $distance = $searchParams['Location__Distance'];
             $distance = isset($distance['value']) ? (float) $distance['value'] : false;
             
             // remove default Location Filters
             $context->removeFilterByName('Location.Lat');
             $context->removeFilterByName('Location.Lng');
          
             if (!$distance || !$lat || !$lng)
                return $query;

             if (!$query->isJoinedTo('Location')) {
                $query->leftJoin('Location', '"Location"."ID" = "Record"."LocationID"');
             }
             
             $query->select['LocationDistance'] = '(3959 * acos(cos(radians(' . $lat . ')) * cos(radians("Location"."Lat")) * cos( radians("Location"."Lng") - radians(' . $lng . ')) + sin(radians(' . $lat . ')) * sin(radians("Location"."Lat")))) AS LocationDistance';
             $query->having[] = 'LocationDistance < ' . $distance;
          }
       
          return $query;
       }
    }

  • StevenMayhew
    Avatar
    Community Member
    4 Posts

    Re: [SOLVED] DataObject Search by Distance Link to this post

    Hi Joern,

    I'm currently looking to do a similar thing - I was wondering if you could show me how you store the LatLng values for the search?

    I'm currently storing them as individual values ("Lat" => Text, "Lng" => Text) and can't get anything to work. Help would be appreciated!

    Thanks,
    Steven

    903 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.