Expose roles to views

Question

I am using the role reference module (https://www.drupal.org/project/rolereference) to allow me to add roles to a content type.

For some context, in my case we are managing policies. The policy content type will have fields such as "Owner" and "Sponsor" that will reference roles using the module above.

My issue comes when trying to put together views. I am able to access the role reference field through views without issue, but its sorting is based role id (rid) instead of the role name.

I am attempting to sort by role name, and am unable to create a relationship as Role is not available.

What is the best way to do this? At the moment it appears that writing a custom module that invokes hook_views_query_alter() to alter the query before it runs might be the best solution.

I'm wondering if anyone has had any success exposing roles as a relationship within views?

Thanks in advance.

Answer 1

Turns out this can only be done programatically in a module. Here is an example in case its useful for someone else:

function HOOK_views_query_alter(&$view, &$query) {
    /***** Owner ****/
  if ($view->name == "policy_listing" && $view->current_display == "policy_by_owner") {
    //alter the query so role reference fields are sorted by the role name rather than role id (rid)
    //replace the rid with the actual owner name
    $query->fields['field_data_field_policy_owners_field_policy_owners_rid']['field'] = "name";
    $query->fields['field_data_field_policy_owners_field_policy_owners_rid']['table'] = "role";
    $query->fields['field_data_field_policy_owners_field_policy_owners_rid']['num'] = 2;

    //add the join to the role table
    $join = new views_join();
    $join->table = "role";
    $join->left_table = "field_data_field_policy_owners";
    $join->left_field = "field_policy_owners_rid";
    $join->field = "rid";
    $join->type = "LEFT";
    $join->extra = array();

    $query->table_queue['role'] = array (
      'alias' => "role",
      'table' => "role",
      'relationship' => "field_data_field_policy_owners",
      'join' => $join,
    );
  }
}

In this case I am replacing rid with the actual name of the role in the view so the sorting works. Obviously, replace HOOK with your module name.