How to retrieve all users and all roles from Kohana 3 using Auth module?

Question

Can I recive list of all users with all roles? I need to know all roles of every user in my database. I am using Auth module in Kohana 3.3 with ORM.

Answer 1

Yes you can. But I can not understand why you want to do such a thing suboptimal.

1. FULL ORM:

$users = ORM::factory('Auth_User')->find_all();
foreach($users as $user) {
    $roles = $user->roles->find_all();
    foreach($roles as $role)
        $role->name;
}

2. Universal SQL (hint):

SELECT * FROM users 
INNER JOIN user_roles USING(user_id) 
INNER JOIN roles USING(role_id) 
ORDER BY users.name

Results:

• user_1 role_1
• user_1 role_2
• user_2 role_x

3. Easy display SQL (but you change code on every adding new role)

SELECT users.*, IF(r1.user_id IS NULL, 0, 1) AS role1, IF(r2.user_id IS NULL, 0, 1) AS role2, IF(rn.user_id IS NULL, 0, 1) AS rolen FROM users LEFT JOIN (SELECT user_id FROM user_roles where role_id = 1) AS r1 USING(user_id) LEFT JOIN (SELECT user_id FROM user_roles where role_id = 2) AS r2 USING(user_id) LEFT JOIN (SELECT user_id FROM user_roles where role_id = n) AS rn USING(user_id)

Results: username | role1 | role2 | rolen user1 | 1 | 0 | 0 user2 | 0 | 1 | 1