3 models ID in the same table?

Need Help
#1

Hi everyone,

I have three models : Company, User and Role (President, Controller, etc…).

A User can be attached to many Company. - this is my reality. I can have the same RainLab.User account that will be used for two differents companies.

A Company can be attached to many User.

This is a regular Many-To-Many ($belongsToMany in both models) relationship. (company_id | user_id)

Now, I need to introduce the notion of Role and this is how I am not sure how to do this.

company_id | user_id | role_id ?

A user can have many roles such as ‘President’ and ‘Controller’, all specific to the company. The same user can have different roles in differents companies.

company_id | user_id | role_id
1 | 1 | 3 (controller)
1 | 1 | 7 (president)
2 | 1 | 5 (vice-president)

What is the best way to design this ? I just add the extra field ‘role_id’ as a pivot column and pass it in the attach() ?

Should I create an pivot model ?

I checked in the plugin OctoberTest but didn’t find any case like this…

Thank you.

#2

Yes a pivot table with role_id is the right way to do it.

Pivot model makes it a bit easier.

use October\Rain\Database\Pivot;

class PersonCompanyPivot extends Pivot
{
    public $table = 'xyz_persons_companies';

    public $belongsTo = [
        'role' => Role::class
    ];
}

for your related models you can use

    public $belongsToMany = [
        'persons' => [
            'XYZ\\Models\\Person',
            'table' => 'xyz_persons_companies',
            'pivotModel' => 'XYZ\\Models\\PersonCompanyPivot',
            'pivot' => ['role_id']
        ]
    ];

if you define your relations in your controller, you can use it like this in your fields.yaml:

    pivot[role]:
        label: 'xyz::lang.role.label'
        span: auto
        type: relation
        nameFrom: name
        emptyOption: 'xyz::lang.role.empty_option'
2 Likes
#3

@TimFoerster

I made it worked as you explained. Just for terminology purposes, dealer = company and title = role…

single_title_id
single_title_id1310×459 28.9 KB

My problem is I need many titles to be attached to Mathieu (in this case for example), for this company. He can be a President but also a Controller.

I tried to change the relation from $belongsTo to $belongsToMany like this :

use October\Rain\Database\Pivot;

class PersonCompanyPivot extends Pivot
{
    public $table = 'xyz_persons_companies';

    public $belongsTo**Many** = [
        'role' => Role::class
    ];
}

That being said, Laravel is looking for an external table called ‘XYZ.person_company_pivot_title’ of course. I started to try in this direction but didn’t worked that well and I would like to make sure that’s the way to go…

There is no way to do this “way” below ?

table : xyz_persons_companies

dealer_id | user_id | title_id
1167 | 60 | 1
1167 | 60 | 3

Thank you…

#4

@daft any ideas ? I applied what Tim suggested but my case it slightly different

#5

At first glance, I’d probably design it like this:

User has many Positions
Position belongs to User
Position belongs to Company
Position belongs to Role

1 Like
#6

thanks, I’ve done that.