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('AppProfile'); }
Profile model:
public function user() { return $this->belongsTo('AppUser'); } public function city() { return $this->belongsTo('AppCity'); }
City model:
protected $spatialFields = [ 'location' ]; public function profiles() { return $this->hasMany('AppProfile'); }
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
Advertisement
Answer
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.