Filter by aggregations

[t1gor]

Hi.

Somhow I couldn't find any info on the topic of filtering by aggregates, so here's my quesion. I have two models: Group -{has many}-> Student. I would like to filter all groups with average student age more then Y (age is a calculated field, e.g. AVG(TIMESTAMPDIFF(YEAR, person.dob, NOW())) AS age)

I am pretty new to Laravel in general, so sorry if this is not a question about filters exactly.

Thanks in advance for your help.

[Tucker-Eric]

You can do it a couple different ways.

Using whereRaw():

If you are doing this in the GroupFilter:

public function averageAge($age)
{
    return $this->related('students', function($query) use ($age) {
        return $query->whereRaw('AVG(TIMESTAMPDIFF(YEAR, person.dob, NOW())) < ?', [$age]);
    });
}

Outside of a filter on the model itself:

Group::whereHas('students', function($query) use ($age) {
    return $query->whereRaw('AVG(TIMESTAMPDIFF(YEAR, person.dob, NOW())) < ?', [$age]);
})->get();

Or you can do it with a selectSub() and has():

In the GroupFilter:

public function averageAge($age)
{
    return $this->related('students', function($query) use ($age) {
        return $query->selectSub('AVG(TIMESTAMPDIFF(YEAR, person.dob, NOW()))', 'age')
            ->having('age', '>', $age);
    });
}

Or on the model itself:

Group::whereHas('students', function($query) use ($age) {
    return $query->selectSub('AVG(TIMESTAMPDIFF(YEAR, person.dob, NOW()))', 'age')
        ->having('age', '>', $age);
})->get();

[t1gor]

Thanks a lot for those examples!