Laravel 5 database relation type

2
votes

I would like to use Laravel 5 Models to retrieve a relationship, but i dont know which relation type i should use and how to implement it.

i have 4 database tables:

  1. Users
  2. Roles
  3. Permissions
  4. role_permission

i need to retrieve all the permissions for a "User" based on its "role_id" column.

I've created 3 models:

  1. User
  2. Role
  3. Permission

The database table "users" holds 2 columns:

  1. id
  2. role_id

The database table "roles" holds 2 columns

  1. id
  2. name

The database table "permissions" holds 2 columns

  1. id
  2. name

The database table "role_permission" holds 3 columns that defines which Role is associated to which Permission.

  1. role_id
  2. permission_id
  3. flag

What i want to achieve is the following syntax: 

$user->role // Get the associated "Role"    
$user->role->permissions // Get the associated permissions for a "Role"  
App\Role::find(1)->permissions // Get the associated permissions for a "Role"

i did read the Laravel documentation about model relations but i really dont get it. Does someone understand what i'm trying to achieve and how to implement it in the Models? maybe with some simpel code examples so i can understand the relations and how they work?

Thanks in advance.

1
phpdatabaselaraveleloquent
User has_one role. Roles belongs_to user and has_many permissions. Permissions has_many roles. – Styphon
User has_one role works! Roles belongs_to user does not work since the roles table has no user_id column! All others does not work either, i use the "role_permission" table to associate the roles with the permissions, which is not used in your answer – SirCumz

1 Answers

2
votes

The way that you have your database defined, you have defined the following relationships: a one-to-many between role and users, and a many-to-many between roles and permissions. It can also be stated that a role has many users, a user belongs to a role, a role has many permissions, and a permission has many roles.

In Laravel, one-to-one relationships are modeled using a hasOne/belongsTo set, one-to-many relationships are modeled using a hasMany/belongsTo set, and many-to-many relationships are modeled using a belongsToMany/belongsToMany set.

The relationships are defined in the models below:

User:
Since the users table contains the foreign key to the roles table (role_id), the User model is on the belongsTo side of the one-to-one/one-to-many relationship.

class User extends Model {
    public function role() {
        return $this->belongsTo('\App\Role');
    }
}

Role/Permission:
The many-to-many with permissions is done by both models having a belongsToMany relationship. The Laravel convention for the pivot table name is the combination of the singular table names, in alphabetical order, with an underscore separator, so it should be 'permission_role'. Since the pivot table name doesn't follow convention, it must be specified in the relationship definition. Also, since you have an extra field on the pivot table, you need to specify access to that field with the withPivot() method on the relationship.

class Role extends Model {
    public function users() {
        return $this->hasMany('\App\User');
    }

    public function permissions() {
        return $this->belongsToMany('\App\Permission', 'role_permission')->withPivot('flag');
    }
}

class Permission extends Model {
    public function roles() {
        return $this->belongsToMany('\App\Role', 'role_permission')->withPivot('flag');
    }
}