laravel: hasMany through belongsToMany

i discovered a cool Laravel trick that people don't seem to know about, so i'm making a small write-up on it for posterity.

imagine we have three models: User, Team, and Project. Users and Teams have a many to many relationship: multiple Users can be in the same Team, a User can be in multiple Teams. Teams can have many Projects, but a Project belongs to only one Team. in code:

// User.php
class User extends Model
{
    public function teams(): BelongsToMany
    {
        return $this->belongsToMany(Team::class);
    }
}

// Team.php
class Team extends Model
{
    public function users(): BelongsToMany
    {
        return $this->belongsToMany(User::class);
    }

    public function projects(): HasMany
    {
        return $this->hasMany(Project::class);
    }
}

// Project.php
class Project extends Model
{
    public function team(): BelongsTo
    {
        return $this->belongsTo(Team::class);
    }
}

a common operation in this imagined scenario would be to get all Projects a User has access to through all of the teams they are in. we could write something like this:

// ...in User.php
public function allProjects(): Builder
{
    return Project::whereIn('team_id', $this->teams()->pluck('id'));
}

it's not terrible, i guess. it does let you get the projects, and it returns a Builder so we can add further constraints. but it's got a critical problem: it's not a relationship! we can't use it in with or withCount, nor can we do any of the other cool things we can do with relationships.

we might try to write the following:

// incorrect!
public function allProjects(): HasManyThrough
{
    return $this->hasManyThrough(
        Project::class,
        Team::class,
        'id',            // foreign key on teams table
        'team_id',       // foreign key on projects table
        'id',            // local key on users table
        'id'             // local key on teams table
    );
}

but this doesn't work! hasManyThrough is meant for two hasMany relationships in a row, or a hasOne into a hasMany, but not for belongsToMany. indeed, the following test fails, as the count of allProjects is 2 and not the expected 6:

public function test_user_can_access_projects_through_teams(): void
{
    // create a user and 3 teams with 2 projects each
    $user = User::factory()
        ->hasAttached(
            Team::factory()
                ->count(3)
                ->has(Project::factory()->count(2))
        )
        ->create();

    $this->assertCount(6, $user->allProjects);
}

it's simple: we just define a new many to many relationship between users and projects, but reusing the team_user table as the pivot table, and using team_id as the related key on the projects table, instead of id:

public function allProjects(): BelongsToMany
{
    return $this->belongsToMany(
        // related model
        related: Project::class,
        // pivot table
        table: 'team_user',
        // foreign key on pivot table referencing `parentKey` in the `users` table
        foreignPivotKey: 'user_id',
        // related key on pivot table referencing `relatedKey` in the `projects` table
        relatedPivotKey: 'team_id',
        // referenced key on the `users` table
        parentKey: 'id',
        // referenced key on the `projects` table
        relatedKey: 'team_id',
    );
}

compare with our definition of User::teams(), expanded to show all default arguments:

// in User.php
public function teams(): BelongsToMany
{
    return $this->belongsToMany(
        related: Team::class, // <--- different
        table: 'team_user',
        foreignPivotKey: 'user_id',
        relatedPivotKey: 'team_id',
        parentKey: 'id',
        relatedKey: 'id',     // <--- different
    );
}

here, relatedKey points to the id field of the teams table, instead of pointing to the team_id column of the projects table.

if you want to think about the relationships graphically, we are essentially constructing a relationship that looks like this:

table:   | user |          |      team_user     |          | project |
column:  | id   | <------> | user_id    team_id | <------> | team_id |

we are abusing the fact that for the team --hasMany--> project relationship to work, projects has to have a team_id column, and that belongsToMany lets us specify what column it should use as a related key.

or if you're more comfortable with the actual sql, this is what this relationship generates:

select * from "projects"
    inner join "team_user"
    on "projects"."team_id" = "team_user"."team_id"
    where "team_user"."user_id" = ?

our tests now pass! and critically, it still works if we write it using loadCount, showcasing that it works like a normal relationship!

$user->loadCount('allProjects');
$this->assertEquals(6, $user->all_projects_count);

as far as i'm aware, the only real downside is that the code is ugly and unintuitive.

it's probably less performant than running Project::whereIn('team_id', $this->teams()->pluck('id')), but it's more generic, and it fits better in a Laravel codebase due to being an actual relationship.

but maybe i'm missing something! if you think i am, please do let me know, and i'll add an update to this post.