As in Kohana 3.3. sort roles by number of users?

Question

ORM

Let's say we use the standard Auth module, where there are users and there are roles, how to display all the roles sorted by the number of users?

Answer 1

$data = array();
foreach ( ORM::factory('Role')->find_all() as $role) {
    $data[$role->name] = $role->users->count_all();
}
arsort($data);
echo Debug::vars($data);

Quick fix, not optimal.
That's better:

$data = DB::select(DB::expr('count(user_id) cnt, roles.name'))
    ->distinct(TRUE)
    ->from('roles_users')
    ->join('roles', 'RIGHT')
    ->on('roles_users.role_id', '=', 'roles.id')
    ->group_by('role_id')
    ->order_by('cnt', 'DESC')
    ->execute();
       
echo Debug::vars($data->as_array());