mysql距离查询不能在laravel下工作

Question

I am trying to find the distance between the $eastings $northings coordinates I have stored and the Eastings / Northings coordinates I have stored in a table in mysql. I am using Laravel. For some reason the query is not working. Here is the code:

public function postAnalysis(){

$relevant_home = Input::get('relevant_homes');

$asset = Home::where('Home_name', '=' , $relevant_home)->first();

$Eastings   = $asset->Eastings;
$Northings  = $asset->Northings;

/*
$competitors = Home::raw('SELECT SQRT(POW($Eastings - `Eastings`,2) + POW($Northings - `Northings`,2)) as distance FROM homes HAVING distance <=10 ORDER BY distance ASC')->get();
echo $competitors;
*/



$competitors = DB::table('homes')
                -> select(DB::raw('SQRT(POW('.$Eastings.' - Eastings,2) + POW('.$Northings.' - Northings,2)) AS distance'))
                -> where('distance', '<', 10)
                -> get();

echo $competitors;


}

The error shown is:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'where clause' (SQL: select SQRT(POW(390120 - Eastings,2) + POW(298935 - Northings,2)) AS distance from `homes` where `distance` < 10)

Answer 1

If you're selecting based on a calculated field (generated via DB::raw() in your select list), rather than on an actual table column that is stored on the database, then you need to use a having() clause rather than where() clause.

This isn't specifically a Laravel/Eloquent or an ORM issue, but is a general requirement for SQL.