感谢 EddyTheDove 和 Ohgodwhy,我找到了解决方案。
所以就是这样:
\App\Model\User::whereNotIn('id', $ids)
->where('status', 1)
->whereHas('user_location', function($q) use ($radius, $coordinates) {
$q->isWithinMaxDistance($coordinates, $radius);
})->select('id', 'firstname')
->get();
而在我的UserLocation
模型我有这个本地范围
public function scopeIsWithinMaxDistance($query, $coordinates, $radius = 5) {
$haversine = "(6371 * acos(cos(radians(" . $coordinates['latitude'] . "))
* cos(radians(`latitude`))
* cos(radians(`longitude`)
- radians(" . $coordinates['longitude'] . "))
+ sin(radians(" . $coordinates['latitude'] . "))
* sin(radians(`latitude`))))";
return $query->select('id', 'users_id', 'cities_id')
->selectRaw("{$haversine} AS distance")
->whereRaw("{$haversine} < ?", [$radius]);
}
Ohgodwhy 的原始答案在这里:Laravel 中两点之间的半正矢距离计算 https://stackoverflow.com/questions/37876166/haversine-distance-calculation-between-two-points-in-laravel
EDIT
另一种使用 MySQL 中的存储函数执行此操作的方法:
DELIMITER $$
DROP FUNCTION IF EXISTS haversine$$
CREATE FUNCTION haversine(
lat1 FLOAT, lon1 FLOAT,
lat2 FLOAT, lon2 FLOAT
) RETURNS FLOAT
NO SQL DETERMINISTIC
COMMENT 'Returns the distance in degrees on the Earth
between two known points of latitude and longitude'
BEGIN
RETURN DEGREES(ACOS(
COS(RADIANS(lat1)) *
COS(RADIANS(lat2)) *
COS(RADIANS(lon2) - RADIANS(lon1)) +
SIN(RADIANS(lat1)) * SIN(RADIANS(lat2))
));
END$$
DELIMITER ;
我乘以 111.045 将结果转换为公里。
(我不确定这个值是否正确,我发现许多其他值与这个值相差不远,所以如果有人对此有精确度,那就太好了)
来源文章:https://www.plumislandmedia.net/mysql/stored-function-havesine-distance-computation/ https://www.plumislandmedia.net/mysql/stored-function-haversine-distance-computation/
然后用雄辩的方式:
\App\Model\User::whereNotIn('id', $ids)
->where('status', 1)
->whereHas('user_location', function($q) use ($radius, $coordinates) {
$q->whereRaw("111.045*haversine(latitude, longitude, '{$coordinates['latitude']}', '{$coordinates['longitude']}') <= " . $radius]);
})->select('id', 'firstname')
->get();