Laravel eloquent relationship and pivot tables

Question

Im experimenting with an Laravel application where I have users and teams. The tables looks a little bit like this (simplified):

users

id*
...

teams

id*
...

team_user

team_id*
user_id*
isLeader
confirmed

As you can see, a user can be part of a number of teams, and *he can also be appointed leader of a given team. A team can have multiple leaders.

The user model has the following relationships:

// Returns all the teams connected to the user and where the confirmed timestamp is set
public function teams()
{
    return $this->belongsToMany(Team::class)->wherePivot('confirmed', '!=', null);
}
// Returns all the teams where the user is appointed team leader
public function teamleaderTeams()
{
    return $this->belongsToMany(Team::class)->wherePivot('isLeader', '=', 1);
}

The team has:

public function confirmedUsers()
{
    return $this->belongsToMany(User::class)->where('confirmed', '!=', null);
}

I need something that returns all the users that the user is team leader for. So if you are not a team leader the result would off course be empty, but if you are it should return all the users from all the teams where you are appointed leader.

Ive tried asking around, and have gotten some suggestions, but not really arrived at a solution. I do kindof understand what I want (I think). Sooo... since you can tell which teams a user is team leader for through the teamleaderTeams() relation, I can loop through each and then ask to get all the confirmed users through the confirmedUsers() relation. But I've only managed to accomplish this in the controller and it just seems messy.

I.e. this only crashes the browser (it seems to be in an infinite loop or something, and I dont really understand why).

public function getLeaderForAttribute()
{
        $users = collect();
        foreach($this->teamleaderTeams as $team)
        {
            foreach ($team->confirmedUsers as $user) {
                $users->add($user);
            }
        }
        return $users->unique('id');
}

Anyway, anyone got a nice solution for a teamleaderUsers() relation (not really a good name for it), that returns all the users from all the teams that a given user is team leader for (thats a mouth full)?

For anyone looking for similar answers; Instead of running a foreach in the controller, you can also map through this easily in the view. You can do something like Auth::user()->teamleaderTeams->map->confirmedUsers — Gard

Nikolay Traykov Nikolay Traykov · Accepted Answer · 2020-03-08T01:07:36

I think this is a nice time to use Pivot models. You can define a pivot model by extending the Pivot class. Furturemore, you can define relationships in the pivot model. So, if you have users relationship in your pivot model, you can make a query like this:

TeamUser::with('users')->where('isLeader', 1); // If the pivot model is called TeamUser

Of course you can exclude a specific user as usual:

TeamUser::with(['users' => function($query) {
    $query->where('id', '<>', 1); // If we want to exclude user with id 1
}])
->where('isLeader', 1);

Of course, you can also make an additional where clause in the relatonship:

public function teamLeaders()
{
    return $this->hasMany('users')->where('isLeader', 1);
}

Please read more about it here and here is the API

Good luck!

Laravel eloquent relationship and pivot tables

2 Answers