Calculation of coordinate distance in Laravel
I have a scope in my model that creates an alias and I need to accomplish what is on it, I understand the MySql dose does not allow this.
Standard SQL does not allow you to reference a column alias in the WHERE clause. This limitation is imposed because when the WHERE code is executed, the column value cannot yet be determined.
However, I am wondering if there is a possible job in laravel? My area is creating a distance with an alias 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);
source to share
First, don't forget to remove the keyword static
from the function call, Laravel uses a magic static kind of call Profile::distance
.
Below are two different options for solving the same problem, you are calling \App\Profile::distance($latitude, $longitude, 50)
from your controller anyway .
Option 1
You can do calculations 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, remember 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();
}
source to share