CODEWITHSUNDEEP
phpmysqllaraveleloquentrelational-database
Udders
Udders

Reputation: 6976

laravel model relationship pivot on pivots?

I have hit a problem in my web application, and I am hoping that some kind soul maybe able to offer me some help.

In my application I have users and organisation - an organisation can have many users and a user can have many organisations to my mind this represents a many-to-many relationship?

User n:m Organisation

Meaning that I require a pivot table? Let us call that Organisation_User. At the moment all seems simple enough, however my complication comes that a user in an organisation can have various roles,

1) Admin
2) Manager
3) Finance
4) Basic

The only rule with these roles is that admin superseeds everything else. My problem is that a user a can theoretically be a basic user and also a finance user in the same organisation. I am struggling to work how best to store this data, do I store it directly in the pivot table? Or do I create a pivot of the pivot something like Organisation_User_Roles and if that is the case, is that even possible in Laravel? How would that look?

Upvotes: 1

Views: 1055

Answers (1)

Bogdan Cismariu
Bogdan Cismariu

Reputation: 151

Read the Retrieving Intermediate Table Columns section from

https://laravel.com/docs/5.1/eloquent-relationships#many-to-many

In your pivot table you can add an additional colum roles where you comma separate the roles (names or ids) and build a custom method hasRole in your models.

Beware though, this approach may have scalability problems!

The other way would be to create a Role model and an additional table organisation_user_role with the structure

organisation_id
user_id
role_id

In your User model build a method hasRoleInOrganisation($role_id, $organisation_id) that would query this table.

This approach doesn't have a Laravelish way if querying this table. You wil have to use:

DB::table('organisation_user_role')
    ->whereUserId($this->id)
    ->whereOrganisationId($organisation_id)
    ->whereRoleId($role_id)
    ->count();

Upvotes: 1

Related Questions