如何转换mysql距离查询到Laravel雄辩查询?-(与纬度，经度和半径参数)

Question

我有一个mysql查询，获得组织的最近位置。我想把它转换成Laravel的雄辩查询，这样我就可以得到组织模型的Attributes函数。这可能吗?这是我的代码:

public function getNearesOraganization(Request $request)
{
$mi = 10;
$centerLat = $request->lat;
$centerLng = $request->lng;
// $orgs = Organization::all();
// foreach ($orgs as $key => $value) {
//     $value->distance = $this->distance(floatval($centerLat), floatval($centerLng), floatval($value->lat), floatval($value->lng));
//     dump($value->distance);
// }
$orgs = DB::select(
DB::raw(
"SELECT id,
org_name,
org_street,
org_cityprov,
org_state,
org_zipcode,
org_lat,
org_lng,
( 3958.8  *
acos(
cos( radians($centerLat) ) *
cos( radians( org_lat ) ) *
cos( radians( org_lng ) - radians($centerLng) ) +
sin( radians($centerLat)) *
sin( radians( org_lat ) )
)
)
AS distance
FROM organizations
HAVING distance < $mi
ORDER BY distance ASC"
)
);
foreach ($orgs as $key => $value) {
$value->f_distance = number_format($value->distance, 2);
}
return response()->json($orgs, 200);
}

Answer 1

一种方法是使用

https://github.com/malhal/Laravel-Geographical this lib

那么你的代码将是

public function getNearesOraganization(Request $request)
{
$query = Model::geofence($request->lat, $request->lng, 0, 10);
$orgs = $query->get();
return response()->json($orgs, 200);
}

Answer 2

我使用Laravel Model Scope进行编码

public function scopeNearestInMiles($query, $mi, $centerLat, $centerLng)
{
return $query
->select(DB::raw("*,(3958.8  *
acos(
cos( radians(" . $centerLat . ") ) *
cos( radians( org_lat ) ) *
cos( radians( org_lng ) - radians(" . $centerLng . ") ) +
sin( radians(" . $centerLat . ")) *
sin( radians( org_lat ) )
)
)
AS distance"))
->having('distance', '<', $mi)
->orderBy('distance');
}

控制器

$orgs = Organization::nearestInMiles($mi,$centerLat,$centerLng)->get();