Retrieving distinct relationships of relationships using Eloquent in Laravel

user_4378701 0
Viewed 206

Lets assume I have a model for: Race, Participant, Team
And these relations: Race 1-N Participant N-1 Team

See in another way :

races  1 - N  participants
teams  1 - N  participants

In Laravel terms:

/* Inside Race model */
public function participants()
{
    return $this->hasMany(Participant::class);
}


/* Inside Team model */
public function participants()
{
    return $this->hasMany(Participant::class);
}


/* Inside Participant model */
public function race()
{
    return $this->belongsTo(Race::class);
}
public function team()
{
    return $this->belongsTo(Team::class);
}

In the participants table, It looks like this:

id | team_id | race_id | [a lot of other columns...]
------------------------------
 1 |    1    |    1    |
 2 |    1    |    1    |
 3 |    2    |    1    |
 4 |    2    |    1    |

In the above example, I know that the race ID 1 has 2 teams.

I can count them by doing this:

$race = Race::find(1);
$number = $race->participants()->distinct()->count('team_id');

The problem

Counting the number of teams is cool, but I want to access to the list of corresponding Team model instances, so that I can use them for further operations (within a foreach loop for example).
I tried a lot of things without success.
Something like this:

$teams = $race->participants()->distinct()->[...] // Don't know what to put here

The working equivalent SQL query is:

SELECT teams.* FROM teams
INNER JOIN participants ON participants.team_id = teams.id
INNER JOIN races ON races.id = participants.race_id
WHERE races.id = 1
GROUP BY teams.id

Resulting in this:

teams query

When the participants table contains this:

participant query

I want to know if I can do it with Eloquent instead of using Query/Builder DB:: methods directly ?

EDIT 1

My closest result:

$race->participants()->distinct()->get('team_id')

result 1

And then using this list of team_id I can access to the teams using Team::find([list of IDs]) but it looks greedy to me.

EDIT 2

I forgot some information:

  • The participants.team_id column is NULLABLE because:
    • There are races with teams of participants
    • There are races with participants (without team)
eloquent laravel php
4 Answers

I don't think you can easily access to it through the $race instance.
But, you can use the whereHas method on the Team model:

$race = Race::find(1);
$teams = Team::whereHas('participants', function ($query) use ($race) {
    $query->where('race_id', $race->id);
})->get();  // ->count() also works

See documentation

user_129133940

To get distinct teams from a race model, you could add a many to many relationship between Race and Team with Participant acting as the pivot.

For example,

/* Race */
public function teams()
{
    return $this->belongsToMany(Team::class, 'participants', 'race_id', 'team_id');
}


/* Team */
public function races()
{
    return $this->belongsToMany(Race::class,'participants', 'team_id', 'race_id');
}

Then, you could just do this

$race = Race::find($id);
$teams = $race->teams()->distinct()->get();

to get a list of distinct teams for the race.

And since we set up the relationship on both models, you can now also get distinct races for each team by doing

$team = Team::find($id);
$teams = $team->races()->distinct()->get();
user_45163160

First, let's improve your eloquent relations.

// Race
public function teams()
{
    return $this->hasMany(Team::class);
}
public function withoutTeamParticipants() // change name that suits you
{
    return $this->hasMany(Participant::class);
}


// Team
public function participants()
{
    return $this->hasMany(Participant::class); // In this case make sure that participant.race_id is null
}


// Participant
public function team()
{
    return $this->belongsTo(Team::class); // When no team, then participant.team_id is null
}
public function race()
{
    return $this->belongsTo(Race::class);
}


To access list of participants of a given race:

$race = Race::where('id', 1)->with('teams.participants', 'withoutTeamParticipants')->get();

In your blade you can further use this as:

// List of participants without a team
@foreach ($race->withoutTeamParticipants as $participant)
    {{ $participant->name }}
@endforeach

// List of participants that are with a team
@foreach ($race->teams as $team)
    @foreach ($team->participants as $participant)
        {{ $participant->name }}
    @endforeach
@endforeach
user_53261910

You can directly use hasManyThrough relationship inside Race model for fetching distinct teams associated with the race.

public function distinctTeams()
{
    return $this->hasManyThrough(Team::class, Participant::class, 'race_id', 'id', 'id', 'team_id')->distinct();
}

Now, you can loop over the teams of a race by using $race->distinctTeams

Hopefully this helps.

user_136424470
Related