John Davidson

Message:

I need to list offers based by merchant distance and I want to do it as much as possible by using the laravel query builder. My current code looks like this:

$data['offers']= Offer::

whereHas('merchants', function (Builder $query) {

    $query->where('dd_merchant', '=', '1')->where('status', '=', 'Open');

})

->where('status', '=', 'Loaded')

->where("start", "<=", date("Y-m-d"))

->where("end", ">=", date("Y-m-d"))

->orderBy('title','ASC')

->paginate(20);

What I have tried so far is the following:

$circle_radius = 3959;

$max_distance = 100;

$lat = $customer->latitude;

$lng = $customer->longitude;

$now = date("Y-m-d");



$data['offers']= DB::table(Offer::raw('(SELECT o.*, m.latitude, m.longitude, (' . $circle_radius . ' * acos(cos(radians(' . $lat . ')) * cos(radians(latitude)) *

cos(radians(longitude) - radians(' . $lng . ')) +

sin(radians(' . $lat . ')) * sin(radians(latitude))))

AS distance

FROM offers as o

LEFT JOIN merchants as m on o.mid = m.mid) AS distances'))

->where([['distance','<',$max_distance],['status','Loaded'],['start','<=',$now],['end','>=',$now],['m.dd_merchant','=',1],['m.status','=','Open']])

->orderBy('distance','ASC')

->get();

But it makes no sense that Laravel does not have some kind of a build in function for this.
Is there a better way of doing it without relaying on RAW?

If Laravel does not support a clean way to void raw, maby theres a composer plugin to extend support for a better way?

---

My Answer:
One way to achieve ordering by distance without using raw queries is to use the Haversine formula in a custom query scope. Here's an example of how you can create a custom query scope in your Offer model:

php
// Offer.php

use Illuminate\Database\Eloquent\Builder;

class Offer extends Model
{
public function scopeOrderByDistance(Builder $query, $lat, $lng)
{
$circle_radius = 3959;

$query->selectRaw("*, ($circle_radius * acos(cos(radians($lat)) * cos(radians(latitude)) *
cos(radians(longitude) - radians($lng)) + sin(radians($lat)) * sin(radians(latitude))) AS distance");

return $query->orderBy('distance', 'ASC');
}
}


Then, you can use this custom query scope in your controller like this:

php
$data['offers'] = Offer::whereHas('merchants', function (Builder $query) {
$query->where('dd_merchant', '=', '1')->where('status', '=', 'Open');
})
->where('status', '=', 'Loaded')
->where("start", "<=", date("Y-m-d"))
->where("end", ">=", date("Y-m-d"))
->orderBy('title', 'ASC')
->orderByDistance($customer->latitude, $customer->longitude)
->paginate(20);


This way, you can avoid using raw queries and still achieve ordering by distance in a cleaner way.