Laravel Relationship - Get all teams belongs to organization for user

I have following models in Laravel:

• User Model
• Team Model
• Company Model

And DB tables:

users
- id

company
- id

teams
- id
- company_id  (team can only belong to one company)

// pivot tables
company_user
- user_id
- company_id

team_user
- team_id
- user_id

I'm trying to create relationship on User Model that will get Company With teams that only belongs to that user. I can make it with joins, but I would like to have a relationship. I have tried different packages, but still can't figure it out. It's just sad 8 hours.

In your scenario, a user can work for multiple companies, and each company can have multiple teams. You want to quickly see which companies a user belongs to, along with the specific teams they're part of within those companies.

For this, you can create a function in the User model called "companyWithTeams".

This function will recognize that a user can be linked to many companies, and it uses a pivot table called "CompanyUser" to keep track of these relations. It also gets user_ids from "CompanyUser" to link the right teams. This function will fetch the companies a user belongs to but also immediately fetch the teams within those companies.

User Model:

public function companyWithTeams()
{
    return $this->belongsToMany(Company::class)
                ->using(CompanyUser::class)
                ->withPivot('user_id')
                ->with(['teams' => function ($query) {
                    // Filter teams to those that the user belongs to
                    $query->whereHas('users', function ($query) {
                        $query->where('user_id', $this->id);
                    });
                }]);
}

Company Model:

public function users()
{
    return $this->belongsToMany(User::class)
                ->using(CompanyUser::class)
                ->withPivot('user_id');
}

public function teams()
{
    return $this->hasMany(Team::class);
}

Team Model:

public function company()
{
    return $this->belongsTo(Company::class);
}

public function users()
{
    return $this->belongsToMany(User::class)
                ->using(TeamUser::class)
                ->withPivot('user_id');
}

In your controller, you can access:

$user = User::find(1);
$companyWithTeams = $user->companyWithTeams;

$companyName = $companyWithTeams->name;

foreach ($companyWithTeams->teams as $team) {
    echo $team->name;
}

Here is a similar question:

How to get records from 3 belongsToMany