Trying to Get Data From Db using Form Under 10 Km

Question

i am trying to get data from database under 10 km records through latitude and longitude using form but i am facing erorr

erorr

Illuminate\Database\QueryException SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'having clause' (SQL: select count(*) as aggregate from users having distance < 25)

Web.php

Route::get('/map','MapController@index');

Route::get('/userview','MapController@getUserByLatLong');

Route::post('/showdata','Mapcontroller@showdata');

User.blade.php

<form method="POST" action="{{ action('Mapcontroller@showdata') }}">
@csrf
<lable>Latitude</lable>
<input type="text" name="latitude" /><br>
<lable>Longitude</lable>
<input type="text" name="longitude" /><br>
<lable>Kilomneter</lable>
<input type="text" name="kilometer" /><br>
<input type="submit" name="submit" /><br>
</form>

Controller code

public function showdata(Request $request)
    {
        $this->validate($request, [
            'latitude' => 'required',
            'longitude' => 'required'
        ]);
        $latitude = $request->input('latitude');
        $longitude = $request->input('longitude');

        $users = DB::table('users')
        ->select(DB::raw($latitude,$longitude, 'SQRT(POW(69.1 * (latitude - 24.900110), 2) + POW(69.1 * (67.099760 -longitude) * COS(latitude / 57.3), 2)) AS distance'))
        ->havingRaw('distance < 25')
        ->OrderBy('distance')
        ->paginate(10)
        ->get();
        echo $users;
        return view('userview',['users' => $users]);


    }

Answer 1

Replace ->havingRaw('distance < 25') with having('distance ', '<' , 25)

Answer 2

I think the best solution for you is to add the column distance in your User Table, than add a mutator in your Model like this :

public function setDistanceAttribute($value)
{
    return $this->attributes['distance'] = SQRT(POW(69.1 * ($this->['latitude'] - 24.900110), 2) + POW(69.1 * (67.099760 -$this->['longitude']) * COS(latitude / 57.3), 2));
}

And then update your query :

 $users = DB::table('users')
        ->select(DB::raw($latitude,$longitude))
        ->havingRaw('distance < 25')
        ->OrderBy('distance')
        ->paginate(10)
        ->get();

Hope it works :)

Answer 3

Use whereRaw to use inbuilt method like this:

        $users = DB::table('users')
        ->select('latitude', 'longitude', DB::raw('SQRT(POW(69.1 * (lattitude - 24.900110), 2) + POW(69.1 * (67.099760 -longitude) * COS(latitude / 57.3), 2)) AS distance'))
        ->whereRaw('SQRT(POW(69.1 * (? - 24.900110), 2) + POW(69.1 * (67.099760 -?) * COS(? / 57.3), 2)) < 25', array($latitude, $longitude, $latitude))
        ->orderBy('distance')
        ->paginate(10)
        ->get();

Answer 4

Solved

public function showdata(Request $request)
{
    $this->validate($request, [
        'latitude' => 'required',
        'longitude' => 'required'
    ]);
    $latitude = $request->input('latitude');
    $longitude = $request->input('longitude');
    $users = DB::table('users')
    ->select(DB::raw('id,name,phone,latitude,longitude, SQRT(POW(69.1 * (latitude - '.$latitude.'), 2) + POW(69.1 * ('.$longitude.'-longitude) * COS(latitude / 57.3), 2)) AS distance'))
    ->havingRaw('distance < 25')
    ->OrderBy('distance')
    ->get();
    return view('userview',['users' => $users]);


}