Multi-Tenant App – Getting Username for Users from Pivot Table

I’m developing a multi-tenant application with single database approach. Each query is typically scoped by subdomain_id.

My database is structured as follows –

users : This table is shared by multiple tenants

id | first_name | last_name | created_at | updated_at

subdomains:

id | creator_id | name | slug | created_at | updated_at

subdomains_users: This is my pivot table

id | subdomain_id | user_id | username | created_at | updated_at

The relationships are defined as follows-

User:

public function subdomains(): BelongsToMany
    {
        return $this->belongsToMany(Subdomain::class, 'subdomains_users', 'creator_id', 'subdomain_id')->withPivot(['username'])->as('info')->using(SubdomainsUsers::class);
    }

Subdomain:

public function users(): BelongsToMany
    {
        return $this->belongsToMany(User::class, 'subdomains_users', 'subdomain_id', 'owner_id')->withPivot(['username'])->as('info')->using(SubdomainsUsers::class);
    }

Throughout my application, I’m required to access the username attribute. However, as you can see, this isn’t straightforward; because a user can belong to multiple subdomains.

How do I go about retrieving the username of any user for the current subdomain?

Answer

You can do it like so:

in User model (assuming you have the current subdomain id in the session, or just replace it with what is appropriate for you):

public function getUsernameAttribute()
{
    return $this->subdomains
        ->first(fn($subdomain) => $subdomain->id == session('subdomain_id')) 
        ->pivot
        ->username;
}

and you’ll be able to call $user->username to get the username for the current subdomain