hasMany relation OctoberCMS | Laravel

Question

i'm trying to build backend for the system which already has it database structure established.
During the development process i've faced an issue with getting hasMany relation to work.

I have 2 database tables:

• permissions
• roles

Problem is that in the roles database, structure looks like this

RoleID  |   PermissionID
1   |       1
1   |       2
1   |       3
2   |       3
3   |       5

So as you can see, there are actually 3 entries for role 1, which also linked to 3 permissions.

And for permissions table, there are only 2 columns: id and name

So far i've managed to get it working, however, due to the fact that there are many entries for one role (at least i think so), i'm only getting 1 permission per role.

The question is: How can i get all permissions for one role using relations.

Current code for PermissionModel.php

public $belongsToMany = [
    'roles'  =>  [
        'Vendor\Database\Models\RoleModel',
        'table' =>  'roles',
        'key => 'permissionID',
        'otherKey' => 'id'
    ]
];

Current code for RoleModel.php

public $hasMany = [
    'permissions'    =>  [
        'Vendor\Database\Models\PermissionModel',
        'table'     =>  'permissions',
        'key'       =>  'id',
        'otherKey'  =>  'permissionID'
    ]
];

And when i'm querying the Role model, i'm just getting 1 permission per role entry, so if there are 20 permissions per role, i'll be getting 20 arrays instead of 1 with 20 values.

Any help is really appreciated! Thank you.

SQL Query which will do the job in that case should look something like this (tested and working just fine)

SELECT 
    roles.id as roleID,  
    (SELECT name FROM permissions WHERE permissions.id = 1) as roleName,  
    permissions.id as permissionID,  
    permissions.name as permissionName 
FROM 
    permissions
LEFT JOIN
    roles ON roles.permissionId = permissions.id
WHERE 
    roles.id = 1;

Answer 1

Roles and Permissions have many-to-many relationship, one role has many permissions and one permission has many roles. You are using one-to-many relationship which is wrong. $hasMany is meant to be used for one-to-many relationships. You have to use $belongsToMany instead, for both models. You need to have three tables:

1. Permissions (2 columns: id and name)
2. Roles (2 columns: id and name)
3. Roles_Permissions (2 columns: RoleID, PermissionID)

Take a look at many to many relationships and try to fix your approach.