如何在Laravel QueryBuilder/MySQL Spatial软件包中按距离对查询结果进行排序? [英] How to sort query results by distance in Laravel QueryBuilder / MySQL Spatial package?

问题描述

首先，我想向您展示当前的数据库结构.有三个表:

First I want to show you the current database structure. There are three tables:

菜肴(ID，名称)

位置(ID，名称，坐标(要点))

locations (id, name, coordinates (POINT))

dish_location (location_id，dish_id)

dish_location(location_id, dish_id)

现在，我想实现一个API，该API获取用户的位置(纬度，经度)，并返回按距离(以公里为单位)排序的菜肴清单.我已经有一种方法需要两个纬度和两个经度并给我距离.但是我敢肯定，您可以向我展示一种方法，这是一种直接在MySQL查询中直接执行此操作的性能更高的方法.

Now I want to implement an API which gets the position (latitude, longitude) of the user and returns a list of dishes sorted by the distance in km. I already have a method which takes two latitudes and two longitudes and gives me the distance. But I am sure you can show me a way, which is a more performant way to do this directly in the MySQL query.

其他:我想在API中执行加载更多"功能.因此，我将已收到的商品数进行计数，或者在这种情况下如何解决?

Additional: I want to do a "load more"-function in the API. So I pass the count of already received items or how would I solve this in this case?

我正在将它用于 MySQL Spatial软件包

推荐答案

首先，让我们看一下如何使用基本查询构建器执行此操作.然后，我们将讨论如何使用Eloquent模型执行此查询:

First, let's take a look at how to do this with the basic query builder. Then, we'll discuss how to execute this query with Eloquent models:

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 参数为我们执行自动分页.阅读分页文档以获得更多信息.使用上面的函数，我们可以按以下方式获取分页的结果集:

The ST_Distance_Sphere() function calculates a distance that we can sort results by. Laravel's paginate() method performs automatic pagination for us using the page parameter passed through the request URL. Read the pagination docs for more information. With the function above, we can fetch a paginated result set as follows:

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

...其中 Point 是

...where Point is the Grimzy\LaravelMysqlSpatial\Types\Point class from the package we're using, and 15 is the number of results per page.

现在，让我们尝试使用Eloquent模型来做到这一点.我们将使用本地查询范围来封装创建该部分所需的逻辑进行查询的查询:

Now, let's try to do this with Eloquent models. We'll use a local query scope to encapsulate the logic needed to create the portion of the query that performs the ordering:

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');
    }
}

此实现在模型上使用元数据将表和字段名称添加到查询中，因此如果它们更改了，我们就不需要更新此方法.现在，我们可以使用模型获取有序集合:

This implementation uses metadata on the models to add the table and field names to the query so we don't need to update this method if they change. Now we can fetch the ordered set using the model:

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

$ sortedDishes 是Laravel的 LengthAwarePaginator 的一个实例，它包装了模型的 Collection .如果我们将结果传递给视图，请按照以下方法在Blade模板中显示它们:

$sortedDishes is an instance of Laravel's LengthAwarePaginator which wraps a Collection of the models. If we pass the results to a view, here's how to display them in a Blade template:

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

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

如上所示，分页器提供了便捷方法，我们可以用于轻松在分页结果之间移动.

As shown above, the paginator provides convenience methods that we can use to easily move between paged results.

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

Alternatively, we could use AJAX requests to load the results. Just be sure to pass the current page + 1 in the page parameter of the request data.

这篇关于如何在Laravel QueryBuilder/MySQL Spatial软件包中按距离对查询结果进行排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！