add_relationship and views_join

Question

In hook_views_query_alter(), I am trying to add a join to the user role of the author of the node and filter basing on the role.

        $query->add_relationship('user_role_join', new views_join('users_roles', 'uid', 'users', 'uid','INNER'), 'node');
        $query->add_where(1,'user_role_join.rid','4' ,'=');

It is something like this, but I am missing something. When I run this query the result is always empty. If I create the condition using Views UI, I do get the correct and expected result. So the data is okay, but my code is the problem.

How do I setup this join correctly in hook_views_query_alter()?

Also the following code doesn't work.

$join = new views_join();
$join->table = 'node';
$join->field = 'uid';
$join->left_table = 'users_roles';
$join->left_field = 'uid';
$join->type = 'INNER';
$join->extra = array(
            array(
                'field' => 'bundle',
                'value' => 'node',
            )
        );
$query->add_relationship('user_node_role', $join, 'node');

$query->add_where(1,'user_node_role.rid','4' ,'=');

Answer 1

I found the solution

        $join = new views_join();
        $join->table = 'users_roles';
        $join->field = 'uid';
        $join->left_table = 'node';
        $join->left_field = 'uid';
        $join->type = 'left';

        $query->add_relationship('users_roles', $join, 'node');
        $query->add_where(1,'users_roles.rid','4' ,'=');

This works

Answer 2

It is better to use hook_query_alter() than hook_views_query_alter() because it uses Drupal database abstraction layer and it's by far more easy to implement.

You can add a tag to the view's display you want to modify, roles for example, and check for it at the beginning of the hook:

function MODULE_query_alter(QueryAlterableInterface $query) {
  if ($query->hasAllTags('views', 'roles')) {
    $query->leftJoin('users_roles', 'ur', 'node.uid = ur.uid');
    $query->condition('ur.rid', 4);
  }
}

IMHO this way is more standard.

Answer 3

I think you need to build the new join outside of the $query->add_relationship.

See: https://api.drupal.org/comment/54213#comment-54213