Finding relationship count as per latitude and longitude in Laravel App

user_7713829 0
Viewed 169

I'm trying to build an application on Laravel. I've a model Project which has id, name, state_id, city_id , latitude, longitude, status, and timestamps

I tried adding a nearby feature which finds the project as per the latitude and longitude constrains. My query is:

$projects = Project::where('projects.status', 'saved')
    ->selectRaw("*,
     ( 6371 * acos( cos( radians(?) ) *
       cos( radians( latitude ) )
       * cos( radians( longitude ) - radians(?)
       ) + sin( radians(?) ) *
       sin( radians( latitude ) ) )
     ) AS distance", [request('latitude'), request('longitude'), request('latitude')])->having("distance", "<", request('range')
    ->with('state', 'city')
    ->orderBy('projects.updated_at', 'desc')
    ->paginate(50)

This works as desired, Now I need to find out count of projects associated with states/city as per the above query, I tried:

$states = State::withCount(['projects' => function ($q){
    $q->selectRaw("*,
         ( 6371 * acos( cos( radians(?) ) *
           cos( radians( latitude ) )
           * cos( radians( longitude ) - radians(?)
           ) + sin( radians(?) ) *
           sin( radians( latitude ) ) )
         ) AS distance", [request('latitude'), request('longitude'), request('latitude')])->having("distance", "<", request('range')
    }])
    ->orderBy('states.name', 'desc')->paginate();

I'm getting an error with MySQL

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'having clause' (SQL: select states., (select count() from projects where states.id = projects.state_id.....

What is the better way to implement this?

eloquent laravel mysql php
2 Answers

The issue is likely to be Eloquent prefixing the database and table names to distance in the having() call.

As distance was an alias created in selectRaw, you will need to use havingRaw(). https://laravel.com/docs/8.x/queries#havingraw-orhavingraw

->havingRaw('distance < ?', request('range'))

user_22867360 
$lat = $request->latitude ?? '';
$lng = $request->longitude ?? '';
$distance = $request->range ?? '';
$states = State::query();
$states = $states::withCount(['projects' => function ($q) use ($lat,$lng,$distance){
    $haversineSQL = "( 6371 * acos( cos( radians('{$lat}') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('{$lng}') ) + sin( radians('{$lat}') ) * sin( radians( latitude ) ) ) )";
    $states = $states->whereRaw($haversineSQL . '< ?', $distance);
}])->orderBy('name', 'desc')->paginate(2);

OR

$states = State::withCount(['projects' => function ($query) use ($lat,$lng,$distance){
        $query->select(\DB::raw('*,( COALESCE( 6371 * acos( cos( radians(' . $lat . ') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(' . $lng . ') ) + sin( radians(' . $lat . ') ) * sin( radians( latitude ) ) ) ) ) AS distance'))->having('distance', '<', $distance );
    }])->orderBy('name', 'desc')->paginate(2);
user_122138960
Related