CODEWITHSUNDEEP
laravellaravel-4eloquent
Rich Bradshaw
Rich Bradshaw

Reputation: 73045

Using complex calculated values with Eloquent

I have two tables, shops and locations.

Right now I'm doing this:

$shops = Shop::with('location');
if (Input::has('location')) {
    $shops->whereHas('location', function($q) use ($input) {
        $q->where('name', '=', Input::get('location');
    });
}

return $shops->paginate(10);

which is all going fine.

However, I'd like to also include a distance value, and order by it. This basically amounts to creating a calcuated value, which I tried doing like this:

$shops->raw('(
    3959 * acos (
        cos ( radians(?) )
   * cos( radians( locations.lat ) )
   * cos( radians( locations.lon ) - radians(?) )
        + sin ( radians(?) )
   * sin( radians( locations.lat ) )
        )
) AS distance
', array(Session::get('latitude'), Session::get('longitude'), Session::get('latitude')));

(put before the return value above)

This doesn't do anything - swapping to a whereRaw doesn't work either.

Am I going to have to stop using Eloquent (again!), or is this something Eloquent can deal with?

Upvotes: 2

Views: 1233

Answers (2)

Rich Bradshaw
Rich Bradshaw

Reputation: 73045

After some help on IRC this is the way I settled on doing it:

// First, specify a manual join onto locations, so the values are available in this query
$shops->join('locations', 'shops.location_id', '=', 'locations.id');

// Second, add the selectRaw (which seems to not have variable binding?)
$shops->selectRaw('(
    3959 * acos (
        cos ( radians('.Session::get('latitude').') )
* cos( radians( locations.lat ) )
* cos( radians( locations.lon ) - radians('.Session::get('longitude').') )
        + sin ( radians('.Session::get('latitude').') )
* sin( radians( locations.lat ) )
        )
) AS distance
');

// select the rest of the variables on shops afterwards to avoid any name collisons
$shops->addSelect('shops.*');

Upvotes: -1

Jarek Tkaczyk
Jarek Tkaczyk

Reputation: 81187

  1. You need selectRaw or select(DB::raw(...)), not whereRaw

  2. You need to join locations table to select from it (to attach distance on Shop objects) OR put that part in the query loading Location models (to attach distance to Location objects):

    Shop::with(['location' => function ($q) {
   $q->selectRaw(' *, YOUR_CALC_HERE as distance'); 
   // don't forget to select all with '*'
}]);

If you want to order shops by distance, then you definitely need the join.

Upvotes: 2

Related Questions