Eli K Miller Eli K Miller - 7 months ago 43
PHP Question

Using a calculated value in a query scope with Eloquent

I have two Eloquent models,

Camp
and
Level
.

A
Camp
has a
start
and an
end
date field.

A
Level
has a
min_age
and a
max_age
integer field.

One
Level
is related to Many
Camps


Using a query scope and a date parameter
$dob
, I would like to fetch a collection of
Camp
s where the age of someone born on
$dob
will fall between
min_age
and
max_age
on the
start
date.

In other words (some pseudo-code):

select * from Camps
join Levels on Camps.level_id = Levels.id
where min_age <= (start - $dob)->toYears <= max_age


Is something like this possible using query scopes, and perhaps without using any raw queries? Maybe like this?

public function scopeAgeAppropriate($query, $dob) {
return $query->join('Levels', 'Camps.level_id', '=' ,'Levels.id')
->where('min_age', '<=', start->diffInYears($dob))
->where('max_age', '>=', start->diffInYears($dob));
}

Answer

Querying through relationships whereHas() and using TIMESTAMPDIFF to calculate the age on the fly:

Your scope will be:

public function scopeAgeAppropriate($query, $dob) {
    return $query->whereHas('levels',function($q) use ($dob) {
        return $q->where('min_age', '<=', \DB::raw("TIMESTAMPDIFF(YEAR, '{$dob}', Camps.start)"))
        ->where('max_age', '>=', \DB::raw("TIMESTAMPDIFF(YEAR, '{$dob}', Camps.start)"));
    })

}