1

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.

1 Answer 1

2

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.

9
  • Quick questions @Jukebox .... how to I filter the results so it only shows Roles I want to give counts for? I don't want to show all Roles Jun 20, 2012 at 9:24
  • You can add a WHERE clause to the query just before GROUP BY. Specify the role IDs like so: WHERE role.rid IN ("1", "2")
    – Jukebox
    Jun 20, 2012 at 19:21
  • Thanks @Jukebox ... I got an ajax error. I have three roles currently.. 100482362 115362423 and 139881272 so i did: WHERE role.rid IN ("100482362", "115362423", "139881272") Can you see where I went wrong? Jun 20, 2012 at 20:30
  • I don't have my test site in front of me at the moment but without knowing what the error said, I would guess that the error might lie in "WHERE role.rid"... try changing that to "WHERE users_roles.rid"... but that's a shot in the dark.
    – Jukebox
    Jun 20, 2012 at 21:05
  • 1
    I can confirm that works without the quotes. Nice work!
    – Jukebox
    Jun 21, 2012 at 15:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.