Eloquent: Get ‘max’ values from a `hasMany` -> `hasOne` relationship

I have an interesting eloquent challenge and my knowledge is not quite there to work it through, hoping you smart folks can give me some guidance. I have read this: Getting just the latest value on a joined table with Eloquent and it is very close to what I want, but I have an extra level of tables in there. I have two tables like this:

Users have many Assessments. Each Assessment hasOne body_fat_testing (and 4 other hasOnes that I need to use as well).

Each body_fat_testing table has several columns I’m interested (see below) in. I’d like to get the “best” result from each “grandchild”, per user.

So out of all the assessments, the best lean_mass, body_fat_percentage, etc I have this so far:

$users = User::with(
    [
        'trainer',
        'firstAssessment.body_fat_testing',
        'firstAssessment.circumference_measurement',
        'firstAssessment.exercise_testing',
        'firstAssessment.overhead_squat_movement_screen',
        'firstAssessment.push_ups_movement_screen',
        'lastAssessment.body_fat_testing',
        'lastAssessment.circumference_measurement',
        'lastAssessment.exercise_testing',
        'lastAssessment.overhead_squat_movement_screen',
        'lastAssessment.push_ups_movement_screen',
    ]
)->find($request->input('user_ids'));
...
(in User.php)

public function lastAssessment()
{
    return $this->hasOne('AppAssessment')->latest('assessment_date');
}

public function firstAssessment()
{
    return $this->hasOne('AppAssessment')->orderBy('assessment_date');
}

There are about 30 total values I want the “best” of. Any ideas on how to accomplish this without looping through each and every value individually?

assessment table:

+----+---------+-----+--------+---------------+
| id | user_id | age | weight | assessed_date |
+----+---------+-----+--------+---------------+
|  1 |       7 |  24 |    204 | 2019-10-29    |
+----+---------+-----+--------+---------------+

body_fat_testing table:

+----+---------------+-----------+---------------------+
| id | assessment_id | lean_mass | body_fat_percentage |
+----+---------------+-----------+---------------------+
|  1 |             1 |    130.97 |               21.10 |
+----+---------------+-----------+---------------------+

Answer

Try this approach (it assumes you have models for your three tables and the relationships between them exist):

First, you’ll relate your User model to your BodyFatTesting model, with this:

public function bodyFatTestings() {
    return $this->hasManyThrough("AppBodyFatTesting", "AppAssessment");
}

Then, you’ll create a secondary method bestBodyFatPercentage like this:

public function bestBodyFatTesting() {
    return $this->bodyFatTestings()->max('body_fat_testing.body_fat_percentage');
}

To use it, just get a model in whichever way you prefer and call bestBodyFatTesting().

From there I think you can adapt it to your other “has ones”.

Advertisement

Answer