Working with many-to-many relations - filters and roles

Hi all,

I'm working on a project based on a legacy database structure. This turns out to be easier than I expected so I'm happy with that. Now I run into design/architectual issue/question with the m2m relationships. Let me explain....

I have users that can have multiple roles in multiple organizations. For this I have four tables:

users
organizations
users_organizations - user_id, organization_id, role_code
roles - role_id, role_code, role_name

With a hasManyThrough I relate the users to the organization through the users_organizations table. Because the entities are related through other fields than the primary key I need to configure those too. This works fine.

    public function users()
    {
        return $this->hasManyThrough(
            'App\User',
            'App\UsersOrganization',
            'organization_cmpwwn',
            'cmp_wwn',
            'cmp_wwn',
            'user_cmpwwn'
        )->select(['role_code'])->where('role_code', '=', 'ContactOrg');
    }

Questions

My current hasManyThrough filters the users so I only get users with a role code of 'ContactOrg'. This is not flexible of course. What is the advised way to proceed?

At some points in my code I want to know if a user has a specific role in a specific organization. This is the reverse of the shown hasManyThrough. What is the trick to get this info? Maybe I can distill this from the answer to my previous question.

How would I incorporate the roles table so I can show role names in my application?

Thank you in advance for thinking with me.

Cheers! Rik

rumm.an

8 years ago

Best Answer

Level 17

Quite simple,

public function users()
{
        return $this->hasManyThrough(
            'App\User',
            'App\UsersOrganization',
            'organization_cmpwwn',
            'cmp_wwn',
            'cmp_wwn',
            'user_cmpwwn'
        );
}

You can access any other rolecodes like so: $organisation->user()->select(['role_code'])->where('role_code', ContactOrg).

If you have a limited number of role_codes you can create a method for each one fo them in your Organisation Model like so:

public function contactOrgUsers()
{
        return $this->users()->select(['role_code'])->where('role_code', 'ContactOrg')->get();
}

and you can acces them this way $organisation->contactOrgUsers().

In case you have unknown number of role_codes you can use arguments:

public function usersWithRole(String $roleCode)
{
        return $this->users()->select(['role_code'])->where('role_code', $roleCode)->get();
}

Hope this helps. :)

rikw

8 years ago

Level 1

Hi @rumm.am thanks for thinking with me! I'll try it this evening. For a specific user I would add his ID to there where as well?

rumm.an

8 years ago

Level 17

I dont think you need to add user's ID anywhere.

rikw

8 years ago

Level 1

Thx @rumm.am this helped me out. Now I filter on multiple role_codes and find I get users double. I added a distict() to fix this.

Please or to participate in this conversation.