CODEWITHSUNDEEP
mysqllaraveleloquent
james
james

Reputation: 2663

Converting SQL Query into Eloquent

I have the following sql query that I need to convert into a Eloquent query I have the lat and lng fields in the user table. After some searching I found there is a $user->whereRaw() method on eloquent but that is as far as I got. I need to select all users within the 25 mile radius.

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

It should work as $user->whereRaw() I think at least that is all I was able to find.

I need to mention that I am doing many queries before and after this, such as 

$user->where()
$user->where()
Then this query needs to run
$user->where()
and finally
$user->get()

Upvotes: 1

Views: 68

Answers (1)

Leo
Leo

Reputation: 7420

You are aggregating data in this case, so eloquent builder its used mostly for known database attributes, you may need to use a raw expression in query. To create a raw expression, you may use the DB::raw, and mixed eloquent query will be:

$markersModel->select(DB::raw('users.id, ( 3959 * acos( cos( radians(37) ) * cos( radians( users.lat ) ) * cos(radians( users.lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( users.lat ) ) ) ) AS distance'))
     ->where('distance', '<' ,25)
     ->orderBy('distance')
     ->take(20)
     ->get();

simple enough, and don't forget to import use DB;

UPDATE

Should not be a problem you can keep chaning the builder until finally you execute it with get() so this then will be:

$user->where()
$user->where()
$user->select(DB::raw('users.id, ( 3959 * acos( cos( radians(37) ) * cos( radians( users.lat ) ) * cos(radians( users.lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( users.lat ) ) ) ) AS distance'))
     ->where('distance', '<' ,25);
$user->where()
and finally
$user->orderBy('distance')
     ->take(20)
     ->get();

Upvotes: 1

Related Questions