Create a list and count of users per role

Question

Using Drupal 7 I would like to display:

A) a list of recent users per role (Limit to 10).

and

B) a total count of users per role.

I have looked for a module to do this but can't find anything. I also tried a few things in views but had no luck. This is what I tried in views:

enable aggregation for you view, and then set the group by for you field (Content:nid for example) to Count.

Didn't give me the results I was looking for.

If there is a module that can do this that would be perfect otherwise i'm hoping to use views... lastly I guess it could be posible with PHP.

Answer 1

I'm going to assume Part A is the easy part, but just to be thorough:

1. Create a new view based on the Users table
2. Add the User: Name field (if it isn't already there)
3. Limit the results to 10
4. Sort by descending

As for Part B, let's begin by dissecting why Aggregation will not work right out of the box with Views 3.

The only reasonable base table that we could use to count user roles is the Users table, right? After all, Views gives us a User: Roles field right away. So, we turn on Aggregation on two copies of the User: Roles field (the first to group the results, and the second to COUNT the results). However, if you take a look at the SQL, the query is:

SELECT users.uid AS uid, COUNT(users_roles.rid) AS users_roles_rid
FROM {users} users
LEFT JOIN {users_roles} users_roles ON users.uid = users_roles.uid
GROUP BY uid

So, the query will:

• SELECT a uid and then COUNT the roles that particular uid possesses
• Perform a LEFT JOIN by comparing two uids from the users table and the users_roles table (not very helpful)

It turns out that the COUNT does its job correctly by counting up the roles each user has... but it is not what we need. Since we chose User as the base table, the COUNT is only counting the number of roles. What we actually need is to COUNT how many users belong to each role. The users_roles table will provide us the necessary information.

Here is the SQL we're after:

SELECT role.name, COUNT(users_roles.uid) AS users_per_role
FROM users_roles AS users_roles
INNER JOIN role AS role ON role.rid = users_roles.rid
GROUP BY role.name

The above query will give you a list of all of the roles in your site along with a count. Since users_roles is not an available base table out of the box, we cannot construct this query via Views without some PHP customization.

There are a few options to get the required results:

• Use the Views API to describe users_roles to Views. This requires a bit of PHP heavy lifting. Check out this answer for more information. The Data module may be of use as well.

• Cheat and run the above query through db_query and stick the results in $view->attachment_after.

Here is an example of the latter, using hook_views_pre_render in a custom module (be sure to clear caches first to get this hook to work):

function mymodule_views_pre_render(&$view) {
  switch($view->name) {
    case 'my-view-name':
      $results = db_query('
        SELECT role.name, COUNT(users_roles.uid) AS users_per_role
        FROM users_roles AS users_roles
        INNER JOIN role AS role ON role.rid = users_roles.rid
        GROUP BY role.name
      ')->fetchAll();

      foreach($results as $result) {
        $rows[] = array($result->name, $result->users_per_role);
      }

      $view->attachment_after = theme('table',
        array(
          'header' => array('Role', 'Number of Users'),
          'rows' => $rows,
        )
      );
      break;
  }
}

Lastly, keep in mind that a user may have multiple roles. So, it would be incorrect to sum up all of the "Users Per Role" values and say that the result is your total number of users.