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