Laravel 5.3 eloquent 3 table join using models

Question

I read this post which helped, but need more guidance please.

I need a set of Results for a particular $batteryid (on Result) and a particular $age (on Test model) and a particular $gender (from Athlete). Each Test belongs to one athlete. Each test has many results.

Model Battery:

class Battery extends Model{
    public function results(){
        return $this->hasMany('App\Result');
    }    
}

Model Result:

class Result extends Model{
    public function test(){
        return $this->belongsTo('App\Test', 'test_id');
    }

    public function battery(){
        return $this->belongsTo('App\Battery', 'battery_id');
    }    
}

Model Test:

class Test extends Model{
    public function athlete(){
        return $this->belongsTo('App\Athlete', 'athlete_id');
    }

    public function results(){
        return $this->hasMany('App\Result');
    }
}

I get the correct results with the following, but it's two queries:

$tests = Test::whereHas('Athlete', function($query) use($gender){
                $query->wheregender($gender);
           })->where('age', $age)->get();

$test_ids = $tests->pluck('id');
$results = Result::where('battery_id', '=', $battery)
          ->whereIn('test_id', $test_ids)
          ->get();

I'd like to use model approach, but am completely stuck. test.age not recognised and not sure how to get the age from athlete model. My attempt:

            $results = Result::with('test')
            ->where('test.age', '=', $age)
            ->join('batteries', 'batteries.id', '=', 'test.battery_id')
            //->where('test->athlete().gender', '=' $gender)
            ->where('battery_id', '=', $battery)
            ->get();

geckob · Accepted Answer · 2017-02-21 14:25:15Z

1

You can use whereHas for the condition and with method for eager loading it.

    $results = Result::whereHas('test', function($q) use ($age) {
          $q->where('age', '=', $age);
          $q->whereHas('athlete', function ($q) {
              $q->where('gender', 'male');
          });

        })
        ->with('test')
        ->whereHas('battery', function($q) use($battery) {
            $q->where('battery_id', '=', $battery);
        })
        ->with('battery')
        ->get();

edited Feb 21, 2017 at 14:25

answered Feb 21, 2017 at 12:56

geckob

8,2085 gold badges36 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

louisav Over a year ago

It worked before you added the gender bit. I'm testing different syntax now, perhaps the 2nd $q-> must come out?

geckob Over a year ago

Does your athlete table got gender column?

geckob Over a year ago

If you use gender variable, you need to include it in function closure.

louisav Over a year ago

yes, I added use($gender). Also tried the gender hard coded exactly as your code. Getting parse error for unexpected }. I've moved things around, not working yet. Works 100% before gender validation.

geckob Over a year ago

I think I fixed it @louisav

|

Collectives™ on Stack Overflow

Laravel 5.3 eloquent 3 table join using models

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related