Damon Damon - 4 months ago 14
PHP Question

Laravel: How can I use where on a with statement?

I have a url that looks like this:

site.com/athletes/1

Each athlete can have many positions. From the url above, I am trying to get all athletes that belong to the position id of 1. My relationship is working great, but I am returning all athletes regardless of the url segment.

I have 3 tables:
athletes
,
positions
&
athlete_position
.

athletes
id
name

positions
id
name

athlete_position
athlete_id
position_id


Athlete.php

public function positions()
{
return $this->belongsToMany('App\Position', 'athlete_position', 'athlete_id', 'position_id');
}


Position.php

public function athletes()
{
return $this->belongsToMany('App\Athlete');
}


AthleteController.php

public function position(Position $position) {
$athletes = Athlete::with('positions')->get();

dd($athletes); // returns all athletes with the position relationship
}


It's like I need something like this:

$athletes = Athlete::with('positions')->where('position_id', '=', $position->id)->get();


But that's not using the relationship correctly. Is there a way to use a
where
clause in the model? That way when I visit the url
site.com/athletes/2
I am only getting back the athletes who belong to position id of 2?

Thank you for any suggestions!

EDIT

Everything is working great! Thank you so much @lagbox! Turns out once again I was over thinking everything.

Just a quick follow-up question. What is the best way to handle when an athlete belongs to position 1 and position 2?

When looping through the athletes in my view, should I just check the position id against the url segment to only show the specific position?

Something like:

@foreach($athletes as $athlete)
@foreach($athlete->positions as $position)
@if($position->id == {{ Request::segment(3) }}
// Show the athlete
@endif
@endforeach
@endforeach


Otherwise, since the athlete belongs to position id 1 and 2 that athlete will show up twice in the results. I only want the athlete to show up once - for the requested position.

For example:
site.com/athletes/position/1


This is what I am seeing now.

Athlete
name: Foo
position_id: 1

Athlete
name: Foo
position_id: 2

Athlete
name: Bar
position_id: 1

Athlete
name: Bar
position_id: 2


From the same url above, this is what I would like to get back:

Athlete
name: Foo
position_id: 1

Athlete
name: Bar
position_id: 1

Answer

You can use whereHas to check for existence of a relationship based upon conditions.

Though it seems like you should be able to just use the Position model instance you have for this as you have the inverse relationship setup already.

$athletes = $position->athletes;
// or to also have all those athletes positions
$athletes = $position->athletes()->with('positions')->get(); // egaer loading
// or if you have `athletes` loaded already
$athletes = $position->athletes->load('positions'); // with lazy eager laoding

I have a small Eloquent Relationships article that goes over some ways to query relationships asklagbox - blog - Eloquent Relations

Updated question: You probably don't need the athletes' positions at all since you know all the athletes you current have in that collection all have this particular one. If that is the case you can just return the $position to the view.

return view(..., ['position' => $position]);

Athletes for Position: {{ $position->name }}
<ul>
@foreach ($position->athletes as $athlete)
    <li>{{ $athlete->name }}</li>
@endforeach
</ul>