Laravel Orderby Calculated Value

Question

I have this function which should return values by distance, closer to further away. However receiving this error in Laravel: Use of undefined constant lat - assumed 'lat'

Code as follows:

public static function getNearby($lat, $lng, $distance = 50, $limit = 50) {

    $radius = 6371.009; // Earths radius in KM

    // Latitude Boundaries
    $minLat = (float) $lat - rad2deg($distance / $radius);
    $maxLat = (float) $lat + rad2deg($distance / $radius);

    // Longitude Boundaries
    $minLng = (float) $lng - rad2deg($distance / $radius / cos(deg2rad((float) $lat)));
    $maxLng = (float) $lng + rad2deg($distance / $radius / cos(deg2rad((float) $lat)));

    // Query DB
    $nearby = (array) DB::table('users')
        ->where('lat', '>', $minLat)
        ->where('lat', '<', $maxLat)
        ->where('lng', '>', $minLng)
        ->where('lng', '<', $maxLng)
        ->orderBy(ABS(lat - (float) $lat) + ABS(lng - (float) $lng), 'ASC')
        ->take($limit)
        ->get(); 

    var_dump($nearby);

}

Any suggestions here? I envisage I may have to do a DB:raw but am unsure how to incorporate that (if I have to)... Appreciate the help, thanks.

Answer 1

You should use the **DB::raw()** function of eloquent.

In your case, the following query should work:

->orderBy(DB::raw('ABS(lat - '.(float)$lat.') + ABS(lng - '.(float)$lng.')'),'ASC' )

Some more DB::raw usage Example

User::select(DB::raw('count(*) as user_count, status'))->first();
User::select(DB::raw('count(*) as user_count'),'status')->first();
User::select(DB::raw(1))->first();

Answer 2

Way too late to help (but for posterity): wouldn't you want to use the Pythagorean equation for the raw SQL?

->orderBy(
  DB::raw('SQRT( POWER(lat - ' . (float)$lat . ', 2) + POWER(lng - ' . (float)$lng . ', 2) )',
  'asc'
)

The actual distance you want to order by is the radius (hypotenuse), correct?