laravel-5 - 通过关联表在 Laravel 5.1 中按距离排序

Question

我正在尝试订购 events通过他们与用户提交的邮政编码和距离的距离。

我附上了我的数据库表及其关系的示例，如您所见 geom通过 postcode 与多个地址相关联地址可以关联到多个表(在本例中是事件表)。



我从最终用户那里获取邮政编码以及以英里为单位的半径来检索适当的事件，这是我如何在 Eloquent 中实现这一目标的示例。

/**
 * Extend locale method which initially only gets lat/long for given postcode to search
 *
 * @param \Illuminate\Database\Eloquent\Builder $query The query builder
 * @param \App\Http\Requests\SearchRequest $request The search request
 * @return void
 */
protected function locale(Builder $query, SearchRequest $request)
{
    $postcode = $this->formatPostcode($request->postcode);

    $geom = Geom::query()->where('postcode', $postcode)->first();
    if (! $geom || Cache::has('postcodeAPIFailed')) {
        return;
    }

    $lat = $geom->geo_location['lat'];
    $long = $geom->geo_location['long'];

    // Top-left point of bounding box
    $lat1 = $lat - ($request->within / 69);
    $long1 = $long - $request->within / abs(cos(deg2rad($lat)) * 69);

    // Bottom-right point of bounding box
    $lat2 = $lat + ($request->within / 69);
    $long2 = $long + $request->within / abs(cos(deg2rad($lat)) * 69);

    $query->whereHas('address', function (Builder $query) use ($request, $lat, $long, $lat1, $long1, $lat2, $long2) {
        $query->whereHas('geom', function (Builder $query) use ($request, $lat, $long, $lat1, $long1, $lat2, $long2) {
            $query->whereRaw('st_within(geo_location, envelope(linestring(point(?, ?), point(?, ?))))', [$long1, $lat1, $long2, $lat2]);
        });
    });
}

在我们检索到搜索结果后，在 Controller 中，我们计算每个结果的距离。

if ($request->has('postcode')) {
    $postcodeDistances = $this->getDistances($results, $request);
}

这将生成一个带有 key of postcode 的数组和 value of distance , 即 $postcodeDistances['L1 0AA'] = '3'; ，我们将此数组发送到 View 。

在 View 中，我们然后使用以下逻辑在适用的记录上显示距离

@if($postcodeDistances)
    <span>
        {{ $postcodeDistances[$result->address->postcode] }}
        mile{{ $postcodeDistances[$result->address->postcode] != 1 ? 's' : '' }} away
    </span>
@endif

我尝试了几种方法，但一直无法更新我的 function locale()按距离排序。我已经考虑过也许我可以将距离附加到集合并使用 Laravel 方法以这种方式对集合进行排序，但如果后者甚至可能的话，从数据库层实现这一点将是理想的。

我的第一次尝试是在 whereHas('geom') 之后添加选择距离字段并按新字段排序
$query->addSelect(\DB::raw("ST_DISTANCE_SPHERE(geo_location, POINT({$long}, {$lat})) AS distance"));
我收到以下错误:

SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 2 column(s) (SQL: select count(*) as aggregate from `event` where (select count(*) from `address` where `address`.`addressable_id` = `event`.`id` and `address`.`addressable_type` = event and (select count(*), ST_DISTANCE_SPHERE(geo_location, POINT(-2.717472, 53.427078)) AS distance from `geom` where `geom`.`postcode` = `address`.`postcode` and st_within(geo_location, envelope(linestring(point(-3.6903924055016, 52.847367855072), point(-1.7445515944984, 54.006788144928))))) >= 1) >= 1 and (select count(*) from `organisation` where `event`.`organisation_id` = `organisation`.`id` and `status` = 1) >= 1 and `event_template_id` is not null and `date_start` >= 2018-07-31 00:00:00 and `status` in (1, 5))

我还尝试在同一个地方使用 orderByRaw，虽然我没有收到错误，但结果没有相应地排序。
$query->orderByRaw('ST_DISTANCE_SPHERE(geo_location, POINT(?, ?)) ASC', [$long, $lat]);

Answer 1

我会去解决你的问题。但。正如我在评论中提到的，您必须将 geo_location 拆分为 lat 和 lng。

完成后，公式如下。这将计算以公里为单位的距离。

$distance = 50; //max distance in km
$limit = 100; //the amount of selected records
$earthRadiusKm = 6371;
$earthRadiusMiles = 3959;
$postcode = $this->formatPostcode($request->postcode);
$geom = Geom::query()->where('postcode', $postcode)->first();
$lat = $geom->lat;
$lng = $geom->lng;

//assuming your Geom model db name is geoms and the 'id' is id
$postcodeDistances = \DB::table('geoms')->selectRaw("
        geoms.id,  ( $earthRadiusKm * acos( cos( radians($lat) ) * cos( radians( geoms.lat ) ) 
        * cos( radians( geoms.lng ) - radians($lng) ) + sin( radians($lat) ) *
        sin(radians(geoms.lat)) ) ) AS distance, lat, lng
    ")->havingRaw("distance < $distance")->orderBy('distance')->limit($limit)->get();

为了您的兴趣，如果您想要以英制英里为单位的结果，我在这两个指标中都添加了地球半径。由于该公式确实利用了地球的半径，因此距离越远，精度越高。

结果 (json) 应如下所示(2 条记录结果，第一个坐标始终是起点)。仅供引用，坐标在菲律宾。

all: [
   {#3627
     +"id": 1128,
     +"distance": 0.0,
     +"lat": "15.6672998",
     +"lng": "120.7349950",
   },
   {#3595
     +"id": 1535,
     +"distance": 9.564007130831,
     +"lat": "15.6732128",
     +"lng": "120.6458749",
   },
]