How to show stores with nearest long/lat values using search function - Laravel 5 eloquent

Question

Ideally I am looking for an Laravel 5 relevant answer. I am trying to make a store locator app. I am up to the point where I'm trying to match one pair of lat/long coordinates (calculated from an address that a user enters into search box) with the nearest (within 100km radius) coordinates/existing stores in my database.

The user enters an address which is converted (using geocoding) into lat and lng coordinates. These are sent to my 'articles' page which has a list of stores & a google map. I used a simple tutorial about scope search to show my articles/stores based on their text 'address'. But this obviously doesn't work for two coordinates. I have a table called Articles which has: id, address, lat, lng, website, title etc..

I need something like this or this but using eloquent.

Current Article Model:

    public function scopeSearch($query, $search){

    return $query->where('address', 'LIKE', "%$search%" );
   }

Articles Controller

 public function index(Request $request)
{
     $query = $request->get('q');
     $articles = $query
    ? Article::search($query)->get()
    : Article::all();
    return view('articles.index', compact('categories'))->withArticles($articles);
}

Current form/ geocode:

    {!! Form::open(['method' => 'GET', 'id' => 'searchForm', 'name' => 'searchForm', 'route' => 'articles_path']) !!}
      {!! Form::input('search', 'q', null, ['placeholder' => 'LOCATION', 'class' => 'locationSearch', 'id' => 'searchInput'])!!}
      {!! Form::hidden('lat', null, ['id' => 'lat'])!!}
      {!! Form::hidden('lng', null, ['id' => 'lng'])!!}
      {!! Form::submit('MAKE ME HAPPY', array('id' => 'submitButton')) !!}
    {!! Form::close() !!}

 <script>
$('#submitButton').on('click', function(event){
    event.preventDefault();
    var address = $('#searchInput').val();
    geocoder = new google.maps.Geocoder();
    geocoder.geocode({
        'address': address
    }, function(results, status) {
        if (status == google.maps.GeocoderStatus.OK) {
             var lat = results[0].geometry.location.lat();
             var lng = results[0].geometry.location.lng();
             $('#lat').val(lat);
             $('#lng').val(lng);
          $('#searchForm').submit();

        } else {
            alert("Geocode was not successful");
        }
    });
});
</script>  

Answer 1

mySQL apparently has a native function to deal with this! https://tighten.co/blog/a-mysql-distance-function-you-should-know-about

public function scopeCloseTo(Builder $query, $latitude, $longitude)
{
    return $query->whereRaw("
       ST_Distance_Sphere(
            point(longitude, latitude),
            point(?, ?)
        ) * .000621371192 < delivery_max_range
    ", [
        $longitude,
        $latitude,
    ]);
}

Answer 2

Better solution is to use this function as a scope. Like that:

Article model:

public static function scopeGetByDistance($query,$lat, $lng, $distance)
{
  $results = DB::select(DB::raw('SELECT id, ( 3959 * acos( cos( radians(' . $lat . ') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(' . $lng . ') ) + sin( radians(' . $lat .') ) * sin( radians(lat) ) ) ) AS distance FROM articles HAVING distance < ' . $distance . ' ORDER BY distance') );

    if(!empty($results)) {

        $ids = [];

        //Extract the id's
        foreach($results as $q) {
           array_push($ids, $q->id);
        }

        return $query->whereIn('id',$ids);

    }

    return $query;

 }

Articles Controller :

public function index(Request $request) {

    $lat = $request->get('lat');
    $lng = $request->get('lng');
    $distance = 1;

    $articles= Article::scope1()->scope2()->scope3()->GetByDistance($lat, $lng, $distance);

   return $articles; 

}

Answer 3

I ended up following this advice. I don't know if it's the 'eloquent' way, but it works. I'm just unsure about the 'distance' and what to use there.

Article model:

public static function getByDistance($lat, $lng, $distance)
{
  $results = DB::select(DB::raw('SELECT id, ( 3959 * acos( cos( radians(' . $lat . ') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(' . $lng . ') ) + sin( radians(' . $lat .') ) * sin( radians(lat) ) ) ) AS distance FROM articles HAVING distance < ' . $distance . ' ORDER BY distance') );

  return $results;
}

Articles Controller

    public function index(Request $request)
{

$lat = $request->get('lat');
$lng = $request->get('lng');
$distance = 1;

$query = Article::getByDistance($lat, $lng, $distance);

    if(empty($query)) {
      return view('articles.index', compact('categories'));
    }

    $ids = [];

    //Extract the id's
    foreach($query as $q)
    {
      array_push($ids, $q->id);
    }

    // Get the listings that match the returned ids
    $results = DB::table('articles')->whereIn( 'id', $ids)->orderBy('rating', 'DESC')->paginate(3);        

    $articles = $results;

    return view('articles.index', compact('categories'))->withArticles($articles);

}