Converting SQL Query into Eloquent

Question

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()

you can just perform normal query and hydrate the result into the model you want. — Wreigh
– Wreigh, Commented Dec 20, 2017 at 4:00

Leo · Accepted Answer · 2017-12-19 23:11:38Z

1

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();

edited Dec 19, 2017 at 23:11

answered Dec 19, 2017 at 21:34

Leo

7,4105 gold badges28 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

james Over a year ago

Thanks for that I should have mentioned that I was doing many queries before and after this and at the ending getting the results. I updated my question accordingly.

Leo Over a year ago

no problem with that :) keep chaining eloquent builder until u finally exe with get(); @james

james Over a year ago

You changed it to 'tableneme.distance' in this second query is that correct? Or should it just be 'distance'? I will try this ASAP.

Leo Over a year ago

not only distance since that will throw ambiguous exception. you should add the table name that contains that column that you are trying to access!

james Over a year ago

Im am sorry, but I am confused. It is the user table, but there is no column 'distance'. I am storing the "lat" and "lng" in the users table and everything in the user table. There is no distance column I thought that was 'AS distance' was for.

|

Collectives™ on Stack Overflow

Converting SQL Query into Eloquent

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related