CODEWITHSUNDEEP
phpmysqllaravel-5laravel-query-builder
Adam
Adam

Reputation: 28968

Laravel: Use join and scope query

My user class hasMany address classes. The address class has a scope query function to get all addresses near an area:

public static function scopeGetByDistance($query,$lat, $lng, int $max_distance)
{
     return $query->selectRaw('id, lat, lng, profileAddress.idMember, ( 3959 * acos( cos( radians( '. $lat.') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(' . $lng . ') ) + sin( radians(' . $lat .') ) * sin( radians(lat) ) ) ) AS distance')
                  ->having('distance', '<', $max_distance );
}

I want to get all users who have an address with max_distance to some (lat,lng) point and I want to get them ordered by that distance.

How can I achieve that with the query builder? I tried this:

  $users = \App\User::leftJoin('addresses', function($join){
    $join->getByDistance(44.81, 20.46, 20);
  })
    ->select(DB::raw('MIN(distance) as closest'))
    ->groubBy('user.id')
    ->orderBy('closest')
    ->get();

But this results in

Method Illuminate\Database\Query\JoinClause::getByDistance does not exist.

Upvotes: 0

Views: 1892

Answers (2)

Adam
Adam

Reputation: 28968

Okay I just figured out that join and relationships are not supported by Laravel

This is what I did in the end:

 $users = \App\User::leftJoin('profileAddress','users.id','=','profileAddress.id')
   ->selectRaw('profileAddress.*,   MIN(3959 * acos( cos( radians( '. $lat.') ) * cos( radians( profileAddress.lat ) ) * cos( radians( profileAddress.lng ) - radians(' . $lng . ') ) + sin( radians(' . $lat .') ) *  sin( radians(lat) ) ) ) as closest')
    ->groupBy('users.id')
    ->having('closest', '<', 20)
    ->orderBy('closest')
    ->get();

Upvotes: 1

syed mahroof
syed mahroof

Reputation: 281

try something like this

   $users = DB::table('users as u')
   ->join('address as ad','u.id','=','ad.uset_id');
   ->select(DB::raw('(6371 * acos(cos(radians('.$lat.')) * cos(radians(lat)) * cos(radians(lng) - radians('.$long.')) + sin(radians('.$lat.')) * sin(radians(lat )))) AS distance'))
   ->having('distance','<',$max_distance)         
   ->get();

Upvotes: 0

Related Questions