Laravel order by distance from longitude latitude

Question

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?

I found a similar issue with solution here : https://laracasts.com/discuss/channels/laravel/how-to-group-and-where-condition-on-alias-name — Leena Patel, Nov 13 '22 at 04:34
check if this helpfull: https://stackoverflow.com/questions/16465779/sorting-mysql-query-by-latitude-longitude — always-a-learner, Nov 13 '22 at 04:45
@LeenaPatel thanks, but they are using selectraw witch is the same then using ::raw witch is what I would like to avoid. I want a cleaner solution. — Patrick Simard, Nov 13 '22 at 06:19
@ankitsuthar thanks but I already know what the SQL is. The issu is about finding a clean way to do this and avoiding raw() query — Patrick Simard, Nov 13 '22 at 06:21
Think I foud a package https://github.com/netsells/laravel-geoscope they also use raw but at least the raw part is not direcly in the code and hidden by the package leaving clean code on the dev side. I will try it tomorow and if it works I will post a solution. If you find a better alt let me know! — Patrick Simard, Nov 13 '22 at 06:29

score 0 · Accepted Answer · answered Nov 13 '22 at 19:20

To solve this in a clean way, I used netsells/laravel-geoscope

composer require netsells/laravel-geoscope
php artisan vendor:publish --tag=geoscope

In the model that contains the longitude and latitude col I added the GeoScopeTrait

use Netsells\GeoScope\Traits\GeoScopeTrait;

class Merchants extends Model
{
    use GeoScopeTrait;
    //
}

My query then looked like this:

$data['offers']= Offer::
whereHas('merchants', function (Builder $query) {
    $customer = DB::table('customers')->where('user_id', '=', Auth::user()->id)->get();
    $query->where('dd_merchant', '=', '1')
    ->where('status', '=', 'Open');
    if (isset($customer->latitude) && isset($customer->longitude)){
        $query->addDistanceFromField($customer->latitude, $customer->longitude);
    }
})
->with("merchants")
->where('status', '=', 'Loaded')
->where("start", "<=", date("Y-m-d"))
->where("end", ">=", date("Y-m-d"))
->paginate(20);

It looks a lot cleaner than using raw functions to achieve the same result!

Laravel order by distance from longitude latitude

1 Answers1