Get all profiles in lat/long + range (laravel - laravel-mysql-spatial)

Question

I am trying to build a form in my laravel app to get all users' profiles that are in or x km around one selected city from my cities table. I'm trying to use the laravel-mysql-spatial that makes use of the ST_DISTANCE_SPHERE mysql function but cant really wrap my brain around the eloquent stuff needed

Basicly each profile is linked to a certain city that has a location POINT() field but i would like to be able to return all profiles that are x km around a city i select via a form.

User model:

public function profile() {
    return $this->hasOne('App\Profile');
}

Profile model:

public function user() {
    return $this->belongsTo('App\User');
}
    
public function city() {
    return $this->belongsTo('App\City');
}

City model:

protected $spatialFields = [
    'location'
];

public function profiles() {
    return $this->hasMany('\App\Profile');
}

the laravel-mysql-spatial has the following function to calculate distance between 2 points

distanceSphere($point, $point, $range);

but cant really figure out how/where to plug it in to get the desired output

User::with(['reviews', 'profile', 'profile.media','profile.city', 'categories'])->where(['type' => 'mester'])

this is gets my users/profiles

if (!empty($request->get('city_id')))
    $users_query->whereHas('profile', function (Builder $query) use ($request) {
        $query->where(['city_id' => $request->get('city_id')]);
    });

and im using this to filter by city when selecting a city.

$request->input('range')

im getting this via a range input as distance

I've already looked similar questions up but wasnt able to wire everything together to get the desired output. Any help would be greatly apreciated

Answer 1

I think you can use whereHas on the profile.city instead of the profile:

$otherCityPoint = City::where('id', $request->city_id)->value('location');
$users_query->whereHas('profile.city', function (Builder $query) use ($request,$otherCityPoint) {
    $query->distanceSphere('location', $otherCityPoint, $request->input('range') * 1000);  // 3 arg is meters
});

This should return all users who have profiles which have a city which is within the given distance.