get by latitude and longitude in laravel 5 with other joins
Below code would be able to retrieve the results in the way you wanted. Code explanation in-line.
if($request->input("postcode")) { $curl = curl_init(); curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false); curl_setopt($curl, CURLOPT_RETURNTRANSFER, true); curl_setopt($curl, CURLOPT_URL, "http://api.postcodes.io/postcodes/" . $request->input('postcode')); $result = json_decode(curl_exec($curl)); curl_close($curl); $postcode_lat = $result->result->latitude; $postcode_long = $result->result->longitude; $query = DB::table('dogs'); //Join statement responsible for retrieving dogs addresses based on latitude and longitude in address table. $query->join(DB::raw('(SELECT dog_id, ( 3959 * acos ( cos ( radians($postcode_lat) ) * cos( radians( lat ) ) * cos( radians( long ) - radians($postcode_long) ) + sin ( radians($postcode_lat) ) * sin( radians( lat ) ) ) )AS distance from dog_addresses) as dog_addresses'), function ($join){ $join->on('dogs.id', '=', 'dog_addresses.dog_id') }); $query->leftJoin('dog_videos','dogs.id','=','dogs_videos.dog_id'); $query->leftJoin('dogs_breeds','dogs.breed_id','=','dogs_breeds.id'); if($request->input("breed") && $request->input("breed") != "" && $request->input("breed") != "any") { $breed = Dog_Breed::where("breed_name", $request->input("breed"))->first(); $query->where('dogs.breed_id', $breed->id); } $results = $query->orderBy('dog_addresses.distance', 'ASC') //Ordering the results in ascending order by calculated distance ->limit(20) //Limiting the results to 20 . Can be changed or removed according to your needs ->get(); //Retrieving the results}
You can try the GLength function of mysql:
SELECT *, GLength(LineString(GeomFromText('POINT(latPosCode,lngPosCode)'),GeomFromText('POINT(latInDataBase,lngInDataBase)'),)) AS DISTANCEFROM dogs ORDER BY DISTANCE