Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

We've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

Data Model Questions /

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

[SOLVED] DataObject Search by Distance


Go to End


3 Posts   2247 Views

Avatar
joern

Community Member, 28 Posts

9 May 2011 at 11:44pm

Edited: 10/05/2011 2:14am

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

Avatar
joern

Community Member, 28 Posts

10 May 2011 at 8:05pm

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;
	}
}

Avatar
Stevie

Community Member, 5 Posts

14 June 2011 at 2:36pm

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