Laravel Raw Query does not recognise alias-CodePudding

What i want: I have a model and want to fire a raw query. I use a selectRaw() for this. Inside the selectRaw I pass the select string with an as as alias. When I fire the query I get the desired list including the alias.

Problem: But now I want to filter the alias. For this I use where Raw. I would also like to change the pure order. For this I use whereRaw() and orderRaw(). Unfortunately, I then get an error message:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Distance' in...

My query:

Model::selectRaw('col1,col2,col3,
            (6368 * SQRT(2*(1-cos(RADIANS(lat))))) AS Distance')
            ->whereRaw('Distance <= 3')
            ->orderByRaw('Distance')
            ->take(20)
            ->get();

Question: Why does it not recognise the alias Distance?

CodePudding user response：

See Mysql Doc about aliases.

It simply says, that you can't use alias in where clauses, simply because it is not evaluated when where runs.

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined. For example, the following query is illegal:

Instead you can use HAVING or eloquent having function.

Model::selectRaw('
  col1,
  col2,
  col3, 
  (6368 * SQRT(2*(1-cos(RADIANS(lat))))) AS Distance
')
->having('Distance', '<=' '3')
->orderByRaw('Distance')
->take(20)
->get();

It should work.