I’m trying to query based on two databases and columns.
$model->where('response_time', '<=', Carbon::now()->subMinutes(DB::raw('anotherTable.created_at'))->diffInMinutes(Carbon::now()))
response_time
contains an integer of minutes, ie: 15
If the anotherTable.created_at
– response_time
(ie, sub 15 mins) is less than the current time, return the row.
I’ve tried the above but I’m having no luck and no idea how to debug my query. Any help appreciated.
Example query that should pass and return its row (1 minute over):
response_time
15
anotherTable.created_at
21-03-2022 13:40:00
Current time: 21-03-2022 13:56:00
Advertisement
Answer
Why Carbon is not working
Carbon
is unable to retrieve the value from the database during query generation on the PHP side ($model->where()
). Carbon
will instead execute immediately on the string
value of 'anotherTable.created_at'
prior to issuing the query to the database. The equivalent of:
$offset = Carbon::now() ->subMinutes('anotherTable.created_at') ->diffInMinutes(Carbon::now()); // 0 $model->where('response_time', '<=', $offset); // WHERE responst_time <= 0
Carbon Process Breakdown
CarbonTraitsDate::__call()
is used to parse the action and units from the method name as sub
and minutes
.
The process conditionally calls CarbonTraitsUnits::subUnit('minute', 'anotherTable.created_at')
[sic] which calls CarbonTraitsUnits::addUnit($unit, -$value, $overflow)
[sic].
The -$value
is ultimately processed by PHP as -'anotherTable.created_at'
resulting in 0
as if you called Carbon::now()->subMinutes(0)
.
The issue would have been pointed out if Carbon
would throw a TypeError
exception when provided a string
for the $value
argument, as opposed to the expected int
. However, the $value
argument of CarbonTraitsUnits::subUnit($unit, $value)
is not type-hinted.
MySQL Resolution
Instead of using Carbon
, use the MySQL TIMESTAMPDIFF()
function to produce the difference in minutes between anotherTable.created_at
and NOW()
in the query.
Provided anotherTable
is being added with a JOIN
in the query.
$model->where( 'response_time', '<=', DB::raw('TIMESTAMPDIFF(MINUTE, anotherTable.created_at, NOW())') );