Multi-Tenant Series With Pivot Table

I am walking through the multi-tenant series, and I have a question about a pivot table in a multi-tenant setup. Since the pivot table is just the link between two tables, do I need a tenant_id on the pivot table?

For example, if I have a users table and roles table there is a many to many relationship. The users table has a tenant_id and the roles table has a tenant_id. Should the role_user pivot table also have the tenant_id field? Or can I assumed that since the users and roles table has the BelongsToTenant trait and corresponding tenant_id field that it cascades through to the pivot table?

sr57

5 years ago

Level 39

Should the role_user pivot table also have ...

No, the pivot table must have only the keys from the 2 original tables.

Mwangaben

5 years ago

Level 5

Yes if you do not want data leak and being able to validate duplication of roles per tenant, But the problem that my arise when you do so is you will get this error

 ` SQLSTATE[HY000]: General error: 1 ambiguous column name: tenant_id `

to resolve this error you need to remove globalScopes on the relationship model like this

 public function roles()
 {
    return $this->belongsToMany(Role::class)
        ->withoutGlobalScopes()
        ->where('pivot_tenant_id', session()->get('tenant_id'))
        ->withPivot('tenant_id');
 }

in order to avoid ambiguous column name: tenant_id which is present on all three tables (roles, users, role_user) Then add back the tenant where condition to fetch only data for that given tenant

Please or to participate in this conversation.