Level 75
That is a nice looking query as is, just use it as is using getPdo().
In fact that is too pretty to mess with.
Jul 13, 2017
4
Level 2
Please help me convert this SQL into an eloquent query
I am trying out a way to conduct a spatial query in eloquent so I can return user models to pass to a view. the aim is search by distance in order of distance. I have this raw query as outlined below
public static function searchByDistance($distance, $unit) {
//spatial queries
$circle_radius = 6371;
// $circle_radius = 3959;
$max_distance = $distance;
$lat = Auth::user()->getLoc()->latitude;
$lng = Auth::user()->getLoc()->longitude;
//get all nearby places
$nearbyusers = DB::select(
'SELECT * FROM
(SELECT users.id, users.country, places.name, places.state_code, places.latitude, places.longitude, (' . $circle_radius . ' * acos(cos(radians(' . $lat . ')) * cos(radians(latitude)) *
cos(radians(longitude) - radians(' . $lng . ')) +
sin(radians(' . $lat . ')) * sin(radians(latitude))))
AS distance
FROM users
LEFT JOIN places ON users.suburb=places.name) AS distances
WHERE distance < ' . $max_distance . '
ORDER BY distance;
');
$nearbyusers = (object)$nearbyusers;
return $nearbyusers;
}
and it returns objects. Is there a way someone could please help me convert it to eloquent? Please?
Level 8
I would leave it too. No need for Eloquent here.
Level 75
@DarkRoast It's a masterpiece that needs framing.
Level 2
thank you good sirs. I very much appreciate your comments. managed to knock this up after a bit of playing around and came up with it.
my next venture will be live messages and notifications! have to figure out how to tackle that. was thinking redis and socketio?
if ( Session::token() !== request( '_token' ) ) {
return Response::json( array(
'searcherror' => 'Invalid Authorization Token'
) );
}
//edit country part later
$point = Place::where('country', 'Australia')->where('state_code', request('state'))
->where('name', request('suburb'))->where('postcode', request('postcode'))->first();
$latitude = $point->latitude;
$longitude = $point->longitude;
$radius = request('distance');
$users = User::select('users.*')
->leftJoin('places', function($join)
{
$join->on('users.suburb', '=', 'places.name');
})
->selectRaw('( 6371 * acos( cos( radians(?) ) *
cos( radians( latitude ) )
* cos( radians( longitude ) - radians(?)
) + sin( radians(?) ) *
sin( radians( latitude ) ) )
) AS distance', [$latitude, $longitude, $latitude])
->havingRaw("distance < ?", [$radius])
->orderBy('distance')
->where('users.country','Australia')
->get();
return Response::json( array(
'searchsuccess' => (String) view('members.yessearch', compact('users'))
) );
Please or to participate in this conversation.