Why can't I use the alias? using raw LARAVEL

Question

The problem is, that the alias fechas doesn't take any value... I can't use it

>    $tabla_c2 = DB::table('horarios')
>     ->select('id_voluntario',DB::raw("DATE_FORMAT(fecha,'%y-%m-%d')as fechas"))
>     ->where('id_voluntario','=', $temp)
>     ->where('fechas','=', $datee)
>     ->get();

This is not a laravel problem, but a SQL limitation. Duplicate of: https://stackoverflow.com/questions/14413867/mysql-select-as-in-where — Jeffrey, Oct 19 '17 at 22:27
Check [this as well](https://stackoverflow.com/questions/200200/can-you-use-an-alias-in-the-where-clause-in-mysql) — ljubadr, Oct 19 '17 at 22:29
Instead of `->where('fechas','=', $datee)` use `->having(''fechas','=', $datee)` — ljubadr, Oct 19 '17 at 22:31
don't use `having` it is slow and uses more resources. Some read material [here](https://stackoverflow.com/questions/328636/which-sql-statement-is-faster-having-vs-where) — Jeffrey, Oct 19 '17 at 22:49

score 1 · Accepted Answer · answered Oct 19 '17 at 23:37

As discussed in the comments, this is a SQL limitation - you cannot use an alias for a column in a where clause.

One workaround, as suggested by @ljubadr, is to use having instead - although I will side with @Jeffrey on this and say it is going to unnecessarily make your query slower.

Rather than use the alias, you can utilise whereRaw():

$tabla_c2 = DB::table('horarios')
 ->select('id_voluntario',DB::raw("DATE_FORMAT(fecha,'%y-%m-%d')as fechas"))
 ->where('id_voluntario','=', $temp)
 ->whereRaw("DATE_FORMAT(fecha,'%y-%m-%d') = ?", $datee)
 ->get();

See the docs under Eloquent Aggregates.

Why can't I use the alias? using raw LARAVEL

1 Answers1