ELOQUENT – Calculating difference of datetimes in where-clause

I am trying to check if the difference of two columns in my database is under a given amount of years – meaning DATETIME – BIRTHDATE < years

I have tried;

$result->where(date_diff((strtotime('datetime')-strtotime('student.birth'), '<', $request->search);

However, I get an error telling me that the;

return type of strtotime is a boolean

Now I’m running out of ideas. Any help would be appreciated.

Edit: for clarification: Value.php:

public function student()
{
    return $this->belongsTo(AppStudent::class);
}

and Student.php

    public function values()
{
    return $this->hasMany(AppValue::class);
}

In the students table I saved:

$table->DateTime('birth');            

In the values table it is:

$table->DateTime('datetime');
$table->unsignedBigInteger('student_id');
$table->foreign('student_id')->references('id')->on('students');

2nd edit:

Thanks to @motia, I’ve edited my code:

$age = $search->age;

This reads the age from the request, working perfectly fine.. but then..:

    $result = Value::query()
        ->whereHas('student', function($q) use($age) {
        $q->whereRaw(
            'TIMESTAMPDIFF(YEAR, students.birth, values.datetime) < ?', [$age]
            );
        })
        ->get(); 

throws an error stating:

SQLSTATE[HY000]: General error: 1 no such function: TIMESTAMPDIFF (SQL: select * from “values” where exists (select * from “students” where “values”.”student_id” = “students”.”id” and TIMESTAMPDIFF(YEAR, students.birth, datetime) < 19))

(19 was my example value for age)

Answer

In your code, you are executing the condition to filter the table in your PHP code rather then building the query and let the database execute the SQL.

When querying you should think of building the correct SQL experession string.

For your case the condition expression you need can not be built with query->where, so you need to use query->whereRaw

Value::query()
// To execute relationships with Elquent use whereHas
// eloquent will generate a "WHERE ... EXISTS" sql expression
->whereHas('student', function ($query) use ($request) {
    // here the $query is executed on the relationship table

    // to get the difference in years use the SQL function TIMESTAMPDIFF
    // its 3rd arg should be the latest date to have positive difference
    $query->whereRaw(
      'TIMESTAMPDIFF(YEAR, students.birth, values.datetime) < ?' , [$request->search]
    );
});

EDIT The example abovee works only on mysql databases. For SQLITE, the condition should be

'(JulianDay(values.datetime) - JulianDay(students.birth)) / 365.25 < ?' 

And for PostgreSQL

'EXTRACT(YEAR from AGE(values.datetime, students.birth)) < ?'