my following code is like this:
$places = DivePlace::selectRaw("*,(st_distance_sphere( POINT(".$lon.",".$lat.") , point(lon, lat))/1000) as distance")
->havingRaw("distance < ".$radius)
->orderBy("distance")
->paginate(10);
without the "havingRaw" everything is good.
After adding it, the following error came up:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in
'having clause' (SQL: select count(*) as aggregate from dive_places
having distance < 300)
Any solution?
->where(DB::raw("(ST_Distance_Sphere(POINT(".$lon.",".$lat."), POINT(lon,lat))/1000)"), '<', 200)
instead of ->havingRaw("(st_distance_sphere( POINT(?, ?) , point(lon, lat))/1000) < ?", [$lon, $lat, $radius])
You need to repeat distance definition because for pagination Laravel uses only count(*)
for columns, so it should be:
$places = DivePlace::selectRaw("*,(st_distance_sphere( POINT(".$lon.",".$lat.") , point(lon, lat))/1000) as distance")
->havingRaw("(st_distance_sphere( POINT(".$lon.",".$lat.") , point(lon, lat))/1000) < ".$radius)
->orderBy("distance")
->paginate(10);
You could also use bindings for the query, so better would be:
$places = DivePlace::selectRaw("*,(st_distance_sphere( POINT(?, ?) , point(lon, lat))/1000) as distance",[$lon, $lat])
->havingRaw("(st_distance_sphere( POINT(?, ?) , point(lon, lat))/1000) < ?", [$lon, $lat, $radius])
->orderBy("distance")
->paginate(10);