[FIXED] How to calculate date time different in Laravel Eloquent based on dynamic column minutes

Issue

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

Solution

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

Carbon\Traits\Date::__call() is used to parse the action and units from the method name as sub and minutes.
The process conditionally calls Carbon\Traits\Units::subUnit('minute', 'anotherTable.created_at') [sic] which calls Carbon\Traits\Units::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 Carbon\Traits\Units::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.

db<>fiddle Example

Provided anotherTable is being added with a JOIN in the query.

$model->where(
    'response_time', '<=',  DB::raw('TIMESTAMPDIFF(MINUTE, anotherTable.created_at, NOW())')
);

Answered By - Will B.

Answer Checked By - Gilberto Lyons (PHPFixing Admin)