laravel 5 - Eloquent Relationships（权限表）

Question

I think am struggling to get the relationships correctly in the scenario I have.

I have three tables

Table: users

|  id  |   username  |
----------------------
|   1  |    pluto    |
|------|-------------|

Table: permissions

|  id  |   user_id   |   level_id   |   app_id   |
--------------------------------------------------
|   1  |      1      |       2      |     9      |
|------|-------------|--------------|------------|

Table: levels

|  id  |    level    |
----------------------
|   1  |    admin    |
|------|-------------|
|   2  |   manager   |
|------|-------------|
|   3  |    editor   |
|------|-------------|

The result I am looking to get is

manager //through User:: model or Auth::

I would like to get a value from levels table in level column either through User model. This is the last version on what I have in my classes...

class User extends Authenticatable 
{
    public function permissions()
    {
        return $this->hasOne('App\Permissions');
    }
}


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

    public function levels()
    {
        return $this->hasMany('App\Levels');
    }
}


class Levels extends Model
{
    public function permissions()
    {
        return $this->belongsTo('Modules\Ecosystem\Entities\permissions');
    }
}

Using this in the controller I am able to retrieve values from permissions table. However I am unable to get values from levels table...

$user = User::with('permissions')->find(Auth::user()->id);

However I am unable to get values from levels table when I try this...

$user = User::with('permissions.levels')->find(Auth::user()->id);

That produces an error:

Column not found: 1054 Unknown column 'levels.permissions_id' in 'where clause' (SQL: select * from `levels` where `levels`.`permissions_id` in (1))

I understand that I am not understanding exactly how relationships would work in this instance but I don't want to just guess a solution. I'd like to understand it.

The thing is ,, Levels table serves only as a list of permission levels (roles). I recognize that I can define permission levels in some other way but for the moment this is how everything is set up.

Answer 1

If you are making a usual user permission system, where 'roles' is replaced by 'levels', then you need to reorganized your tables and relationships.

Table: users

|  id  |   username  |    level_id    |
---------------------------------------
|   1  |    pluto    |       2        |
|------|-------------|----------------|


Table: levels

|  id  |    level    |
----------------------
|   1  |    admin    |
|------|-------------|
|   2  |   manager   |
|------|-------------|
|   3  |    editor   |
|------|-------------|


Table: permissions

|  id  |  level_id   |   app_id   |
-----------------------------------
|   1  |      2      |     9      |
|------|-------------|------------|

So now User

hasOne('App\Levels', 'level_id');

Levels

hasMany('Modules\Ecosystem\Entities\permissions', 'level_id');

Permissions

belongsTo('App\Levels', 'level_id');

That is probably what you were trying to do, and it will works.

However, if many roles may have similar permissions, e.g: admin, manager & editor can all have access to a 'Page' or 'Content' or whatever that they all have access to, then you will need a 4th table to have many-to-many relationship between permissions and levels.

Table: permissions

|  id  |   app_id   |
---------------------
|   1  |     9      |
|------|------------|

Table: levels_permissions

|  level_id   |   permission_id   |
-----------------------------------
|      2      |         1         |
|-------------|-------------------|

With this, in Levels

belongsToMany('Modules\Ecosystem\Entities\permissions', 'levels_permissions', 'level_id', 'permission_id');

Inverse the relation in Permissions

belongsToMany('App\Levels', 'levels_permissions', 'permission_id', 'level_id');

In both approaches, you can now do

$user = User::with('levels.permissions')->find(Auth::user()->id);