Using a calculated value in a query scope with Eloquent

Question

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 Camps 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 1

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)"));
    })

}