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

Question

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('App\Assessment')->latest('assessment_date');
}

public function firstAssessment()
{
    return $this->hasOne('App\Assessment')->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 |
+----+---------------+-----------+---------------------+

Please add your table structure so that we can have a better idea of how to get the best results — Ayrton, Nov 08 '19 at 16:09
You can nest your parameters to `with` more deeply. E.g. `lastAssessment.body_fat_testing.whatever`. You should edit your post to include the other relationships as well as what attempts you've made to solve the problem yourself. — miken32, Nov 08 '19 at 16:13

score 2 · Accepted Answer · answered Nov 08 '19 at 16:23

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("App\BodyFatTesting", "App\Assessment");
}

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".

score 0 · Answer 2 · answered Nov 08 '19 at 16:48

0

You can also do it this way:

    public function bestLeanMass()
    {
        return $this->hasOneThrough('App\BodyFatTesting', 'App\Assessment')
            ->selectRaw('user_id, max(lean_mass) as aggregate')
            ->groupBy('user_id');
    }

Not sure which way I like better. The former is a bit easier to understand but creates more methods. This one is harder to understand but fewer methods.

answered Nov 08 '19 at 16:48

emd

434
1
7
18

1

Having the relationship and the aggregation in separate methods might be better because then you can reuse the relationship for your other aggregates – Ayrton Nov 08 '19 at 16:51

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

2 Answers2