Laravel model scope activated_at and deactivated_at check if has 7 day difference

Question

Using a model scope:

public function scopeApproved($query)
{
    return $query->where('activated_at', '>=', 'deactivated_at' -7 days);
}

I know that this is invalid syntax but thought it might be clear what i am trying to achieve.

'activated_at' and 'deactivated_at' are two fields in the same table i would like to get all instances where they are more then 7 days apart

Answer 1

Shempigon answer is almost correct it only has one issue, you don't wrap the columns name in single quote and also the dates have to be switched, something like this:

public function scopeApproved($query) {
    $query->whereRaw("DATEDIFF(deactivated_at, activated_at) >= 7");
}

This is because expects this input: DATEDIFF(endDate, starDate), you can read more about DATEDIFF here

Answer 2

I'm not sure there is a proper way to achieve this via Eloquent, though you can use MySQL function DATEDIFF. Try something like :

// in your model
public function scopeApproved($query) {
    $query->whereRaw("DATEDIFF('activated_at','deactivated_at') >= 7");
}

This is dependant on the database driver you are using (ie. MySQL, SQLite, SQL server, ...)

EDIT:

Found something else that works (so far) with MySQL :

// in your model
public function scopeApproved($query) {
    $query->whereRaw("TO_DAYS(activated_at) - TO_DAYS(deactivated_at) >= 7");
}

TO_DAYS being a build in MySQL function.