add_relationship and views_join 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-&gt;add_relationship('user_role_join', new views_join('users_roles', 'uid', 'users', 'uid','INNER'), 'node');
        $query-&gt;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-&gt;table = 'node';
$join-&gt;field = 'uid';
$join-&gt;left_table = 'users_roles';
$join-&gt;left_field = 'uid';
$join-&gt;type = 'INNER';
$join-&gt;extra = array(
            array(
                'field' =&gt; 'bundle',
                'value' =&gt; 'node',
            )
        );
$query-&gt;add_relationship('user_node_role', $join, 'node');

$query-&gt;add_where(1,'user_node_role.rid','4' ,'=');
I found the solution

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

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


This works