Joining tables in ORM

0
votes

I'm trying to write my own RBAC module in Kohana. I don't want to use an existing module, I just want to do this to learn.

My tables are: users, roles, permission and users_roles, roles_permissions (because of the many to many relations between users<->roles, and roles<->permissions);

My User model:

class Model_User extends ORM {

    protected $_primary_key = 'user_id';

    protected $_has_many = array(
        'roles' => array(
            'model'   => 'role',
            'through' => 'users_roles',
        ),
    );
}

My Role Model:

class Model_Role extends ORM {

    protected $_primary_key = 'role_id';

    protected $_has_many = array(
        'users' => array(
            'model'   => 'user',
            'through' => 'users_roles',
        ),

        'permissions' => array(
            'model'   => 'permission',
            'through' => 'roles_permissions',
        ),
    );
}

and my Permission model:

class Model_Permission extends ORM {

    protected $_primary_key = 'permission_id';

    protected $_has_many = array(
        'roles' => array(
            'model'   => 'role',
            'through' => 'roles_permissions',
        ),
    );
}

I create users:

$user = ORM::factory('user');
$user->username = 'johndoe';
$user->email = '[email protected]';
//etc
$user.save();

I create roles:

$author = ORM::factory('role');
$author->name = 'author';
$author->save();

I create permissions:

$read = ORM::factory('permission');
$read->name = 'read';
$read->description = 'can read posts';
$read->save();

$write = ORM::factory('permission');
$write->name = 'write';
$write->description = 'can write posts';
$write->save();

I add roles to users:

$user->add('roles', $author);

I add permissions to roles:

$author->add('permissions', $read);
$author->add('permissions', $write);

and everything is working fine.

But, my question is how to check if a user has a given permission: in this case how to check if johndoe has permission to write a post?

Thank you for your help!

3
phpmysqlormkohana
Get all the authors permissions and then go through them and look if the write permission is in that set of permission objects. - hakre
I want to check if $user has write permission, and not if $author has write permission.. - Tamás Pap
Then you first need to get the role(s) of the user, then go through them to look if the permission is given. What's so complicated with that? - hakre
True, but isn't there a more elegant solution. Can't I do this in a single query? - Tamás Pap
You're using an ORM. As I understand you, you should not care about queries when you use the ORM's provided interface. As you want to write your ORM on your own, well, the ORM does what you made it doing. As you don't want to use a ready solution (which is normally highly suggested because ORM is complex software), live with what you have. If you're interested how you can create a sophisticated ORM on your own, there are plenty of books that describe things in detail. But as written, the topic is complex, so expect to read a few thousand pages before you start to code. - hakre

3 Answers

1
votes

One query, but only two joins as compared to the previous answer

class Model_User extends ORM {

// ... some stuff here

public function has_permission($permission_name)
{
    return (bool) ORM::factory('permission')
        ->where('permission.name', '=', $permission_name)
        ->join('roles_permissions')
            ->on('roles_permissions.permission_id', '=', 'permision.permission_id')
        ->join('roles_users')
            ->on('roles_users.role_id', '=', 'roles_permissions.role_id')
            ->on('roles_users.user_id', '=', DB::expr((int) $this->user_id))
        ->count_all();
}

}

0
votes

I found a solution, in 1 query:

$count = ORM::factory('permission')
                            ->join('roles_permissions')
                            ->on('permission.permission_id', '=', 'roles_permissions.permission_id')
                            ->join('roles')
                            ->on('roles.role_id', '=', 'roles_permissions.role_id')
                            ->join('users_roles')
                            ->on('roles.role_id', '=', 'users_roles.role_id')
                            ->join('users')
                            ->on('users.user_id', '=', 'users_roles.user_id')
                            ->on('users.user_id', '=', DB::expr($user->user_id))
->where('permission.name', '=', 'write')
                            ->find_all()
                            ->count();

and then if $count > 0, it means $user has permission to write.

Is there a simpler solution?

-1
votes

Here is a better solution which will perform a faster query when primary key or a model instance is supplied:

<?php

class Model_User extends ORM {

    // ...

    /**
     * Check if a user is allowed to perform an action based on permissions
     * attached to the user's roles.
     *
     * @param  int|string|Model_Permission $permission name, primary key or instance of permission
     * @return boolean TRUE if allowed; FALSE otherwise
     */
    public function is_allowed_to($permission)
    {
        // We have a permission name
        if (is_string($permission) AND ! is_numeric($permission))
            return (bool) ORM::factory('permission')
                ->where('permission.name', '=', $permission)
                ->join('roles_permissions')
                    ->on('roles_permissions.permission_id', '=', 'permission.permission_id')
                ->join('roles_users')
                    ->on('roles_users.role_id', '=', 'roles_permissions.role_id')
                ->where('roles_users.user_id', '=', $this->pk())
                ->count_all();

        // We can get the permission primary key
        // to perform a faster query
        $permission_id = ($permission instanceof Model_Permission)
            ? $permission->pk()
            : $permission;

        return (bool) DB::select(array(DB::expr('COUNT(*)'), 'records_found'))
            ->from('roles_permissions')
            ->join('roles_users')
                ->on('roles_users.role_id', '=', 'roles_permissions.role_id')
            ->where('roles_permissions.permission_id', '=', $permission_id)
            ->where('roles_users.user_id', '=', $this->pk())
            ->execute()
            ->get('records_found');
    }

    // ...
}