在Laravel中计算坐标距离(Calculate coordinate distance in Laravel)

634 IT屋

我的模型中有一个创建别名的范围,我需要在其上执行一个位置,我知道MySql不允许这样做.

标准SQL不允许您在WHERE中引用列别名 条款.施加此限制是因为当WHERE代码为 执行,则可能尚未确定列值.

但是,我想知道是否有laravel可以解决? 我的范围创建了一个别名距离,我想检查该距离.

public static function scopeDistance($query, $lat, $long)
{
    return $query->select(array('*',\DB::raw('( 3959 * acos( cos( radians(' . $lat . ') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(' . $long . ') ) + sin( radians(' . $lat .') ) * sin( radians(latitude) ) ) ) AS distance')));
} 

在我的控制器中:

\App\Profile::distance($latitude, $longitude)->where('distance', '<', 50);
解决方案

首先,不要忘记从函数调用中删除static关键字,Laravel处理具有神奇的静态外观 Profile::distance致电.

下面有2个不同的选项可解决同一问题,无论哪种情况,您都将从控制器中调用\App\Profile::distance($latitude, $longitude, 50).

选项1

您可以在内存中进行计算,而不用处理子查询.

public function scopeDistance($query, $lat, $long, $distance) {
    return Profile::all()->filter(function ($profile) use ($lat, $long, $distance) {
        $actual = 3959 * acos(
            cos(deg2rad($lat)) * cos(deg2rad($profile->latitude))
            * cos(deg2rad($profile->longitude) - deg2rad($long))
            + sin(deg2rad($lat)) * sin(deg2rad($profile->latitude))
        );
        return $distance < $actual;
    });
}

选项2

您可以执行SQL子查询,请确保以get()结束调用:

public function scopeDistance($query, $lat, $long, $distance) {
    return $query->having('distance', '<', $distance)
             ->select(DB::raw("*,
                     (3959 * ACOS(COS(RADIANS($lat))
                           * COS(RADIANS(latitude))
                           * COS(RADIANS($long) - RADIANS(longitude))
                           + SIN(RADIANS($lat))
                           * SIN(RADIANS(latitude)))) AS distance")
             )->orderBy('distance', 'asc')
              ->get();
}

I have a scope in my model which creates an alias and I need to execute a where on it, I understand that MySql dose not allow this.

Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined.

However, I'm wondering if there is a possible laravel work around? My scope creates an alias distance and I want to check the distance.

public static function scopeDistance($query, $lat, $long)
{
    return $query->select(array('*',\DB::raw('( 3959 * acos( cos( radians(' . $lat . ') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(' . $long . ') ) + sin( radians(' . $lat .') ) * sin( radians(latitude) ) ) ) AS distance')));
} 

In my controller:

\App\Profile::distance($latitude, $longitude)->where('distance', '<', 50);
解决方案

Firstly, don't forget to remove the static keyword from the function call, Laravel deals with magical static-looking Profile::distance call.

Below are 2 different options to solve the same issue, in either case you will call \App\Profile::distance($latitude, $longitude, 50) from your controller.

Option 1

You can do the calculation in memory instead of dealing with subqueries.

public function scopeDistance($query, $lat, $long, $distance) {
    return Profile::all()->filter(function ($profile) use ($lat, $long, $distance) {
        $actual = 3959 * acos(
            cos(deg2rad($lat)) * cos(deg2rad($profile->latitude))
            * cos(deg2rad($profile->longitude) - deg2rad($long))
            + sin(deg2rad($lat)) * sin(deg2rad($profile->latitude))
        );
        return $distance < $actual;
    });
}

Option 2

You can execute a SQL subquery, make sure to end the call with get():

public function scopeDistance($query, $lat, $long, $distance) {
    return $query->having('distance', '<', $distance)
             ->select(DB::raw("*,
                     (3959 * ACOS(COS(RADIANS($lat))
                           * COS(RADIANS(latitude))
                           * COS(RADIANS($long) - RADIANS(longitude))
                           + SIN(RADIANS($lat))
                           * SIN(RADIANS(latitude)))) AS distance")
             )->orderBy('distance', 'asc')
              ->get();
}

本文地址:IT屋 » 在Laravel中计算坐标距离

相关文章: