How to get created column using SelectRaw and use it inside whereBetween-CodePudding

I've got a laravel eloquent query which uses selectRaw and whereBetween:

$offset = ' 8:00';
$d["records"] = data_entries::select("*")
     ->selectRaw("CONVERT_TZ (testTime, ' 0:00', '{$offset}') as convertedTime")
     ->whereBetween("testTime", $filters['duration'])
     ->where('data_entries.patientID_FK', '=', $patient['patientID'])
     ->leftjoin('additional_notes', 'ID', '=', 'additional_notes.entryID_FK')
     ->get();

So this query works and I can get convertedTime data. But I want to use convertedTime instead of testTime in whereBetween like this:

$d["records"] = data_entries::select("*")
     ->selectRaw("CONVERT_TZ (testTime, ' 0:00', '{$offset}') as convertedTime")
     ->whereBetween("convertedTime", $filters['duration'])
     ->where('data_entries.patientID_FK', '=', $patient['patientID'])
     ->leftjoin('additional_notes', 'ID', '=', 'additional_notes.entryID_FK')
     ->get();

but I got error saying unknown column 'convertedTime'. Is there a way to get the date that was converted based on timezone inside the whereBetween?

CodePudding user response：

You cannot refer to an alias defined in the select clause in the where clause at the same level of the query. But it just so happens that MySQL has overloaded the HAVING operator such that it can be used in place of WHERE, with the added feature that it can also refer to aliases. Consider the following version:

$d["records"] = data_entries::select("*")
    ->selectRaw("CONVERT_TZ (testTime, ' 0:00', '{$offset}') AS convertedTime")
    ->where('data_entries.patientID_FK', '=', $patient['patientID'])
    ->leftjoin('additional_notes', 'ID', '=', 'additional_notes.entryID_FK')
    ->havingRaw("convertedTime >= ? AND convertedTime <= ?", $filters['duration'])
    ->get();