CODEWITHSUNDEEP
mysqlsqllaravel
CHARITRA SHRESTHA
CHARITRA SHRESTHA

Reputation: 782

how to use the alised column in the where clause in laravel

I want to get all the max_id and min_id of the current date from the positions table by offsetting the timezone by +05:54. I had tried.

$maxminpositionids = DB::table('tc_positions')
        ->select(DB::raw('max(id) maxid, min(id) minid, deviceid, date(CONVERT_TZ(servertime,\'+00:00\',\'+05:45\' )) as serv'))
        ->where('serv', '=', Carbon::now()->toDateString())
        ->get();

and I've got this error error

Is there any way to solve and optimize this query?

Upvotes: 0

Views: 45

Answers (1)

Lajos Arpad
Lajos Arpad

Reputation: 76464

In MySQL you can create column aliases in the select clause, however, you cannot use them in the where clause of the same query, because it's invalid syntax. I can only guess about the cause, which is probably a dependency issue, because when the records are searched for, the engine will check whether they will be in the result set and only then will run the logic in the select clause. This is only a guess, but it makes perfect sense, because the query would be much slower if the selection part would run first, also, we would have issues like needing the original fields and possibly creating an alias which is identical with an existent column name. Regardless of whether I'm right about the cause, you cannot use the column aliases in the where clause. Instead, just call whereRaw and pass the same value to it:

$serv = 'date(CONVERT_TZ(servertime,\'+00:00\',\'+05:45\' ))';
$maxminpositionids = DB::table('tc_positions')
        ->select(DB::raw('max(id) maxid, min(id) minid, deviceid, '.$serv.' as serv'))
        ->whereRaw($serv.'= ?', Carbon::now()->toDateString())
        ->get();

Upvotes: 1

Related Questions