CODEWITHSUNDEEP
phpmysqllaravelapi
Baran KARABOGA
Baran KARABOGA

Reputation: 80

Converting Mysql Nearby Query to Laravel API Query

I'm curious about how can I convert this query to Laravel query.

here is my nearby query I tried on phpmyadmin exactly working.

set @lat=37.034126;
set @lng=27.430235;

SELECT party_title, latitude, longitude, 111.045 * DEGREES(ACOS(COS(RADIANS(@lat))
 * COS(RADIANS(latitude))
 * COS(RADIANS(longitude) - RADIANS(@lng))
 + SIN(RADIANS(@lat))
 * SIN(RADIANS(latitude))))
AS distance_in_km
FROM parties
ORDER BY distance_in_km ASC
LIMIT 0,5

query result :

query results

I tried like this but it didnt work.

 public function index(\Illuminate\Http\Request $request) {
        if($request->has('party_category')){
           $parties = Parties::where('party_category', $request->party_category)->get();//your new query here
        }
        else if($request->has('lat') && $request->has('long')){
            $parties = Parties::whereRaw("ACOS(SIN(RADIANS('latitude'))*SIN(RADIANS($request->lat))+COS(RADIANS('latitude'))*COS(RADIANS($request->lat))*COS(RADIANS('longitude')-RADIANS($request->long)))")->get();
        }else {
            $parties = Parties::all();
        }
        return Fractal::includes('places')->collection($parties,new PartyTransformer);
     }

I'm curious how can I convert query and how can I get distance_in_km value from query becuase I want to show in api response.

Upvotes: 0

Views: 139

Answers (2)

Baran KARABOGA
Baran KARABOGA

Reputation: 80

I fixed problem , here is code I hope it will help somebody : 

    class PartiesController extends Controller
{
        public function index(\Illuminate\Http\Request $request) {
          if($request->has('lat') && $request->has('long')){
                $lat = $request->lat;
                $long = $request->long; 
            $parties=DB::select(DB::raw("SELECT *,111.045*DEGREES(ACOS(COS(RADIANS(':lat'))*COS(RADIANS(`latitude`))*COS(RADIANS(`longitude`) - RADIANS(':long'))+SIN(RADIANS(':lat'))*SIN(RADIANS(`latitude`)))) AS distance_in_km FROM parties ORDER BY distance_in_km asc LIMIT 0,5"), array(
                'lat' => $lat,
                'long' => $long
              ));
            $hidacik = Parties::hydrate($parties);
            return Fractal::includes('places')->collection($hidacik,new PartyTransformer);
             }
           else {
                $parties = Parties::all();
            }
       return Fractal::includes('places')->collection($parties,new PartyTransformer);
        }
}

Upvotes: 1

Amrit G.C
Amrit G.C

Reputation: 46

Replace whereRaw with selectRaw as you are applying select statement on the raw query.

Upvotes: 0

Related Questions