Laravel eloquent relationships, 3 way join

Question

I have 3 tables (simplified here)

users

id, name, email

teams

id, name

team_user

team_id, user_id

I want to send an API query to return all teams a user Id belongs to, and what other members are also in that team. Rather than returning just userIds, I want to fill the arrays with their actual user data, e.g name and email.

Route::get('/user/{id}/teams/', 'UserController@getTeams');

User.php (model)

class User extends Authenticatable
{
    use HasApiTokens, Notifiable;

    public function teams()
    {
        return $this->belongsToMany('App\Team', 'team_user', 'team_id', 'user_id');
    }
}

Team.php (model)

class Team extends Model
{    
    public function users()
    {
        return $this->belongsToMany('App\User', 'team_user', 'team_id', 'user_id');
    }
}

TeamUser.php (model)

class TeamMember extends Model
{    
    public function user()
    {
        return $this->hasOne('App\User');
    }

    public function team()
    {
        return $this->hasOne('App\Team');
    }
}

UserController.php

class UserController extends Controller
{
    public function getTeams($userId) {

        return User::find($teamId)->teams()->get();
    }
}

This returns:

[
    {
        "id": 6,
        "name": "P12",
        "location": "Newbury",
        "owner": 6,
        "active": 1,
        "created_at": "2017-12-20 10:18:00",
        "updated_at": "2017-12-20 10:18:00",
        "pivot": {
            "team_id": 6,
            "user_id": 6
        }
    },
    {
        "id": 4,
        "name": "fwffewfffffff",
        "location": "ffffffff",
        "owner": 4,
        "active": 1,
        "created_at": "2017-12-19 19:56:27",
        "updated_at": "2017-12-19 19:56:27",
        "pivot": {
            "team_id": 6,
            "user_id": 4
        }
    }
]

However I would like to also include a list of other users in each of those 2 teams. With their names and emails (from the users table), not just the user_ids.

Is this possible without doing further seperate queries?

Answer 1

You would be able to eagerly load the relations and attach them to the model.

By using the dot notation teams.users, the output will include all users of all teams attached to user with id $userId.

I've added the findOrFail to make sure it will return a 404 when user can not be found.

class UserController extends Controller
{
    public function getTeams($userId)       
    {
        return User::with('teams.users')->findOrFail($userId);
    }
}

This will return the User and attach the relations.

If you want to just return the teams, you can do something like this:

class UserController extends Controller
{
    public function getTeams($userId)       
    {
        $user = User::with('teams.users')->findOrFail($userId);

        // $user->teams will hold a collection of teams with their users
        return $user->teams;
    }
}

Answer 2

For now I have solved this with

class UserController extends Controller
{
    public function getTeams($userId) {

        $teamWithMembers = [];

        $teams = User::find($userId)->teams()->get();

        foreach($teams as $team) {
            $team->members = Team::find($team->id)->users()->get();
            $teamWithMembers[] = $team;
        }

        return $teamWithMembers;
    }
}

Answer 3

Try using with() to retrieve the fields from different tables, for example

class UserController extends Controller {
    public function getTeams($userId) {
         return User::find($userId)->with('teams')->get();
         // return User::find($userId)->teams()->get();
    }
}

If you would like to select specific column from the team_members database, you could add function inside with, for example

class UserController extends Controller {
    public function getTeams($userId) {
         return User::find($userId)->with(['teams' => function($query) {
             $query->select('id', 'name');
         }])->get();
    }
}