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

Question

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

lagbox · Accepted Answer · 2016-07-10 16:21:10Z

2

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>

edited Jul 10, 2016 at 16:21

answered Jul 10, 2016 at 9:41

lagbox

50.7k8 gold badges76 silver badges85 bronze badges

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

2 Comments

Damon Over a year ago

This is absolutely fantastic. Thank you so much for your response. Your blog post is AWESOME! Everything is working great - I just have a follow-up question. I updated my question.

Damon Over a year ago

AAAHHH! I keep getting tunnel vision on the athlete and losing the fact I can leverage the inverse and use the position to get the athletes as well. Thank you so much for your time!

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related