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.

Data Model Questions /

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

[SOLVED] DataObject Search by Distance


Go to End
Reply


3 Posts   1179 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