尝试使用 10 公里以下的表单从 Db 获取数据

Question

我正在尝试使用形式通过纬度和经度从10公里记录下的数据库获取数据，但我面临着erorr

埃罗尔

Illuminate\Database\QueryException SQLSTATE[42S22]： 列未找到： 1054 “have clause” 中的未知列 'distance' （SQL： select count（*） 作为距离< 25 的聚合）users

网.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>

控制器代码

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

我认为最适合您的解决方案是在用户表中添加列距离，而不是在模型中添加突变体，如下所示：public&nbsp;function&nbsp;setDistanceAttribute($value){ &nbsp;&nbsp;&nbsp;&nbsp;return&nbsp;$this->attributes['distance']&nbsp;=&nbsp;SQRT(POW(69.1&nbsp;*&nbsp;($this->['latitude']&nbsp;-&nbsp;24.900110),&nbsp;2)&nbsp;+&nbsp;POW(69.1&nbsp;*&nbsp;(67.099760&nbsp;-$this->['longitude'])&nbsp;*&nbsp;COS(latitude&nbsp;/&nbsp;57.3),&nbsp;2)); }然后更新您的查询：&nbsp;$users&nbsp;=&nbsp;DB::table('users') &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;->select(DB::raw($latitude,$longitude)) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;->havingRaw('distance&nbsp;<&nbsp;25') &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;->OrderBy('distance') &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;->paginate(10) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;->get();希望它能:)

Answer 2

将&nbsp;->havingRaw（'distance < 25'）&nbsp;替换为&nbsp;have（'distance'， '<' ， 25）

Answer 3

解决public function showdata(Request $request){&nbsp; &nbsp; $this->validate($request, [&nbsp; &nbsp; &nbsp; &nbsp; 'latitude' => 'required',&nbsp; &nbsp; &nbsp; &nbsp; 'longitude' => 'required'&nbsp; &nbsp; ]);&nbsp; &nbsp; $latitude = $request->input('latitude');&nbsp; &nbsp; $longitude = $request->input('longitude');&nbsp; &nbsp; $users = DB::table('users')&nbsp; &nbsp; ->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'))&nbsp; &nbsp; ->havingRaw('distance < 25')&nbsp; &nbsp; ->OrderBy('distance')&nbsp; &nbsp; ->get();&nbsp; &nbsp; return view('userview',['users' => $users]);}

Answer 4

用于使用内置方法，如下所示：whereRaw&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$users&nbsp;=&nbsp;DB::table('users') &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;->select('latitude',&nbsp;'longitude',&nbsp;DB::raw('SQRT(POW(69.1&nbsp;*&nbsp;(lattitude&nbsp;-&nbsp;24.900110),&nbsp;2)&nbsp;+&nbsp;POW(69.1&nbsp;*&nbsp;(67.099760&nbsp;-longitude)&nbsp;*&nbsp;COS(latitude&nbsp;/&nbsp;57.3),&nbsp;2))&nbsp;AS&nbsp;distance')) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;->whereRaw('SQRT(POW(69.1&nbsp;*&nbsp;(?&nbsp;-&nbsp;24.900110),&nbsp;2)&nbsp;+&nbsp;POW(69.1&nbsp;*&nbsp;(67.099760&nbsp;-?)&nbsp;*&nbsp;COS(?&nbsp;/&nbsp;57.3),&nbsp;2))&nbsp;<&nbsp;25',&nbsp;array($latitude,&nbsp;$longitude,&nbsp;$latitude)) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;->orderBy('distance') &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;->paginate(10) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;->get();