Laravel: Use join and scope query

Question

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.

Adam · Accepted Answer · 2018-08-10 05:41:11Z

1

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();

edited Aug 10, 2018 at 5:41

answered Aug 10, 2018 at 5:01

Adam

29.6k30 gold badges187 silver badges281 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

syed mahroof · Accepted Answer · 2018-08-10 05:21:57Z

0

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();

answered Aug 10, 2018 at 5:21

syed mahroof

2916 silver badges20 bronze badges

Collectives™ on Stack Overflow

Laravel: Use join and scope query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related