Hi, we are trying to implement a haversine query using Laravels' join closure but it doesn't want to work.
We created a dummy method to count records based on the passed data (lat, lng and radius) which worked fine.
We just can't seem to get it going inside the join closure...
Dummy Method
public function dummyTestOne($request){
$listings = new BusinessListingsModel();
// General
if($request->has('title') && isset($request->title)){
$listings = $listings->where('title', 'LIKE', '%'.$request->title.'%')->where('listing_status', 1);
}
// Location -> This doesn't work
if($request->has('address_name')){
$title = $request->title;
$lat = $request->lat;
$lng = $request->lng;
$radius = '10';
$listings = $listings->join('business_sites', function($join) use ($title, $lat, $lng, $radius){
$join->on('listings.linked_to_siteId', '=', 'business_sites.id')->selectRaw('( 3959 * acos( cos( radians(?) ) *
cos( radians( location_lat ) )
* cos( radians( location_lng ) - radians(?)
) + sin( radians(?) ) *
sin( radians( location_lat ) ) )
) AS distance', [$lat, $lng, $lat])->having('distance', '<', $radius);
});
}
// Business Details
if($request){
$listings = $listings->join('businesses as business', 'listings.for_businessId', '=', 'business.id')->select('business_name')
->join('business_logos as lg', 'listings.for_businessId', 'lg.business_id');
}
return $listings->select('*')->get();
}
Test Count method -> This works
public function locationCountCheck($lat, $lng){
$radius = 10;
$testGet = BusinessSitesModel::selectRaw('( 3959 * acos( cos( radians(?) ) *
cos( radians( location_lat ) )
* cos( radians( location_lng ) - radians(?)
) + sin( radians(?) ) *
sin( radians( location_lat ) ) )
) AS distance', [$lat, $lng, $lat])->havingRaw('distance < ?', [$radius])->count();
return $testGet;
}
So when we submit a title, with lat and lng it will look for the title first, move onto the location and then the business information.
Title on it's own works perfect, business information works perfect as well, just the location closure causing problems by returning an empty result set. No error
Would be grateful if someone could give us a few pointers or tell us where we are going wrong