如何在Laravel QueryBuilder / MySQL Spatial包中按距离排序查询结果？

Question

首先，我想展示给您当前的数据库结构。有三个表：

dishes (id，name) locations (id，name，coordinates(POINT))

dish_location(location_id，dish_id)

现在我想实现一个API，该API获取用户的位置（纬度、经度），并返回按公里数排序的菜肴列表。我已经有一个方法，它接受两个纬度和两个经度，并给出距离。但是我相信您可以向我展示一种更高效的方法，在MySQL查询中直接完成此操作。

另外：我希望在API中实现"加载更多"功能。那么我如何传递已接收到的项目数量或者在这种情况下应该如何解决呢？

我正在使用MySQL Spatial package。

- rakete

---

这是一个两部分的排序。首先，在用户位置为中心处绘制一个正方形。然后，减去/加上最大搜索距离以获取4个最小和最大点。接着，查询数据库以返回该正方形内的结果。然后，对这个子集使用真实公式（我相信是haversine）执行真正的排序。 - jmbmage

这个解决方案的问题在于，这是一个无限列表。 - rakete

然后你必须找到一种限制列表的方法... - jmbmage

Answer 1

首先，让我们看一下如何使用基本的查询构建器来实现这一点。然后，我们将讨论如何使用Eloquent模型来执行此查询：

function paginateDishesFromPoint(Point $point, $pageSize) 
{
    $distanceField = "ST_Distance_Sphere(locations.coordinates, "
        . "ST_GeomFromText('{$point->toWKT()}') AS distance"; 

    return DB::table('dishes') 
        ->select('dishes.*', DB::raw($distanceField))
        ->join('dish_locations', 'dish_locations.dish_id', '=', 'dishes.id')
        ->join('locations', 'locations.id', '=', 'dish_locations.location_id')
        ->orderBy('distance') 
        ->paginate($pageSize);
}

ST_Distance_Sphere() 函数用于计算可以根据其排序的距离。 Laravel 的 paginate() 方法使用通过请求 URL 传递的 page 参数自动进行分页。阅读 pagination docs 以获取更多信息。使用上述函数，我们可以按以下方式获取分页结果集：

$point = new Point($latitude, $longitude); 
$sortedDishes = paginateDishesFromPoint($point, 15); 

...其中Point是我们使用的Grimzy\LaravelMysqlSpatial\Types\Point类，来自该扩展包，而15是每页结果数。

现在，让我们尝试使用Eloquent模型来实现。我们将使用本地作用域来封装创建执行排序的查询部分所需的逻辑：

class Dish extends Model 
{
    ...

    public function locations() 
    {
        return $this->belongsToMany(App\Location::class);
    }

    public function scopeOrderByDistanceFrom($query, Point $point) 
    {
        $relation = $this->locations();
        $locationsTable = $relation->getRelated()->getTable();
        $distanceField = "ST_Distance_Sphere($locationsTable.coordinates, "
        . "ST_GeomFromText('{$point->toWKT()}') AS distance";

        return $query
            ->select($this->getTable() . '.*', DB::raw($distanceField))
            ->join(
                $relation->getTable(), 
                $relation->getQualifiedForeignKeyName(), 
                '=', 
                $relation->getQualifiedParentKeyName()
            )
            ->join(
                $locationsTable,
                $relation->getRelated()->getQualifiedKeyName(),
                '=', 
                $relation->getQualifiedRelatedKeyName()
            )
            ->orderBy('distance');
    }
}

这个实现使用模型上的元数据将表名和字段名添加到查询中，因此如果它们更改，我们无需更新此方法。现在，我们可以使用该模型获取有序集合：

$point = new Point($latitude, $longitude); 
$sortedDishes = Dish::orderByDistanceFrom($point)->paginate($pageSize);

$sortedDishes 是 Laravel 的 LengthAwarePaginator 实例，它包装了模型的 Collection。如果我们将结果传递给视图，在 Blade 模板中展示如下：

<ul>
    @foreach($sortedDishes as $dish) 
        <li>{{ $dish->name }} is {{ $dish->distance }} meters away.</li>
    @endforeach
</ul>

<a href="{{ $sortedDishes->nextPageUrl() }}">Load more...</a>

如上所示，分页器提供了一些便捷方法，我们可以使用这些方法轻松地在分页结果之间进行移动。

另外，我们也可以使用AJAX请求来加载结果。只需确保在请求数据的page参数中传递当前页面+1即可。

- Cy Rossignol

---

谢谢！这解决了我的问题。你有什么想法，我怎样才能在结果数组中得到菜品的位置作为字段？在模型中，我有一个函数locations()用于位置关系，但我只需要我们正在连接的“一个位置”。 - rakete

不客气！我们可以在调用select()时添加"$locationsTable.name AS location_name"，这将作为$dish->location_name可用。对我来说，“locations”与“dishes”之间是多对多的关系似乎有些奇怪。一个菜品真的可以存在于多个地点吗？ - Cy Rossignol

Answer 2

SQL

SELECT d.name AS `Dish`,
       l.name AS `Location`,
       ST_Distance(l.coordinates, POINT(<<<longitude>>>, <<<latitude>>>)) AS `Distance`
FROM dish_locations dl
JOIN dishes d
ON d.id = dl.dish_id
JOIN locations l
ON l.id = dl.location_id
ORDER BY `Distance`
LIMIT <<<n-1>>>, <<<page size>>>;

我希望您能够将<<<longitude>>>和<<<latitude>>>从用户位置中插入，<<<n-1>>>从已检索到的行数减一中获取，<<<page size>>>作为所需检索的下一行数。

演示

http://rextester.com/YAEBF16430

说明

• ST_Distance_Sphere 用于计算距离，单位为米。
• LIMIT <<offset>>, <<num>> 用于实现分页（其中 offset 是基于零的）。

- Steve Chambers

---

谢谢！现在的重点问题是，您是否知道如何将这个纯SQL查询转换成Laravel代码，使用QueryBuilder（https://laravel.com/docs/5.5/queries）构建此查询。这样我就可以从自动匹配我的模型和加载关系中受益。 - rakete

抱歉，我不熟悉Laravel，但Cy Rossignol的答案似乎解决了一种方法。 - Steve Chambers

Answer 3

Postgis有两个操作符（<->和<#>）用于计算距离和计算KNN（最近邻居）。您可以使用它们来代替st_distance。操作符查询语句应该类似于：

WITH index_query AS (
 SELECT ST_Distance(geom, 'SRID=3005;POINT(1011102 450541)'::geometry) as d,edabbr, vaabbr
FROM va2005
ORDER BY geom <-> 'SRID=3005;POINT(1011102 450541)'::geometry LIMIT 100)
SELECT *
   FROM index_query
ORDER BY d limit 10;

使用距离作为d，您可以排序。

- Marco