How can I pass a previously declared DB::raw() column into a whereRaw() query

Question

I have a laravel eloquent query that gets a users ID, and their distance from your own position

 $lat = Auth::user()->lat;
 $lon = Auth::user()->lon;

 $users = DB::table('users')
 ->select(DB::raw("id, ASIN(SQRT( POWER(SIN(($lat 
- abs(lat))*pi()/180/2),2)+COS($lat*pi()/180 )*COS(abs(lat)*pi()/180)
 *POWER(SIN(($lon-lon)*pi()/180/2),2)))as distance))
->whereRaw("distance < $radius")
>paginate($this->takeResults);

The query works and calculates all the distances. But then I cant query those distances. Because passing 'distance' to the whereRaw() statement doesn't seem to carry over?

I get

Unknown column 'distance' in 'where clause'

Anyone know how I can do this otherwise?

All the other answers say to try HAVING instead of WHERE because its a MYSQL problem. But when i try laravels havingRaw() it still doesnt work?

[Here](https://stackoverflow.com/a/942592/1457270) good answer for this. — nakov, May 21 '19 at 07:27

score 1 · Accepted Answer · edited May 21 '19 at 07:37

1

You can try using havingRaw()

$lat = Auth::user()->lat;
$lon = Auth::user()->lon;

$users = DB::table('users')
    ->select(DB::raw("id, ASIN(SQRT( POWER(SIN(($lat - abs(lat))*pi()/180/2),2)+COS($lat*pi()/180 )*COS(abs(lat)*pi()/180)*POWER(SIN(($lon-lon)*pi()/180/2),2)))as distance"))
    ->havingRaw("'distance' < $radius")
    ->paginate($this->takeResults);

edited May 21 '19 at 07:37

Kylie

11,421
11
47
78

answered May 21 '19 at 07:27

Mehravish Temkar

4,275
3
25
44

Still get "Unknown column 'distance' in 'having clause' " – Kylie May 21 '19 at 07:28

How can I pass a previously declared DB::raw() column into a whereRaw() query

1 Answers1