I want to make a view that shows all nodes where the author has a certain role, but that doesn't work, the generated SQL query is wrong.
Filtering on the author's name does work, as do some other filters, only roles gives a problem.

Test view:

$view = new view;
$view->name = 'Praesidium';
$view->description = '';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = '0';
$view->api_version = 2;
$view->disabled = FALSE; // Edit this to true to make a default view disabled initially
$view->display = array();
  $display = new views_display;
  $display->id = 'default';
  $display->display_title = 'Defaults';
  $display->display_plugin = 'default';
  $display->position = '1';
  $display->display_options = array(
  'style_plugin' => 'default',
  'style_options' => array(),
  'row_plugin' => 'fields',
  'row_options' => array(),
  'relationships' => array(),
  'fields' => array(
    'view_node' => array(
      'id' => 'view_node',
      'table' => 'node',
      'field' => 'view_node',
      'label' => 'Link',
      'text' => '',
      'relationship' => 'none',
    ),
  ),
  'sorts' => array(),
  'arguments' => array(),
  'filters' => array(
    'uid' => array(
      'id' => 'uid',
      'table' => 'users',
      'field' => 'uid',
      'operator' => 'in',
      'value' => '',
      'group' => 0,
      'exposed' => TRUE,
      'expose' => array(
        'operator' => 'uid_oper',
        'identifier' => 'uid',
        'label' => 'Gebruiker: Naam',
        'optional' => 1,
        'single' => 1,
        'remember' => 0,
        'reduce' => 0,
      ),
      'relationship' => 'none',
      'expose_button' => array(
        'button' => 'Hide',
      ),
    ),
    'rid' => array(
      'id' => 'rid',
      'table' => 'users_roles',
      'field' => 'rid',
      'operator' => 'or',
      'value' => array(
        '3' => '3',
      ),
      'group' => 0,
      'exposed' => FALSE,
      'expose' => array(
        'operator' => FALSE,
        'label' => '',
      ),
      'relationship' => 'none',
      'expose_button' => array(
        'button' => 'Expose',
      ),
      'reduce_duplicates' => 0,
    ),
  ),
  'items_per_page' => 10,
);
$view->display['default'] = $display;

Resulting query:
SELECT node.nid AS nid
FROM node node
INNER JOIN users users_roles ON node.uid = users_roles.uid
WHERE users_roles.rid = 3

Error messages:

# user warning: Unknown column 'users_roles.rid' in 'where clause' query: SELECT COUNT(*) FROM (SELECT node.nid AS nid FROM node node INNER JOIN users users_roles ON node.uid = users_roles.uid WHERE users_roles.rid = 3 ) AS count_alias in /var/www/drupal/sites/all/modules/views/includes/view.inc on line 621.
# user warning: Unknown column 'users_roles.rid' in 'where clause' query: SELECT node.nid AS nid FROM node node INNER JOIN users users_roles ON node.uid = users_roles.uid WHERE users_roles.rid = 3 LIMIT 0, 10 in /var/www/drupal/sites/all/modules/views/includes/view.inc on line 646.

Comments

peter_halada’s picture

peter_halada commented

I have found this also, the fix should be done in file user.views.inc, definition of user roles table should be :

// ----------------------------------------------------------------------
// users_roles table

$data['users_roles']['table']['group'] = t('User');

// Explain how this table joins to others.
$data['users_roles']['table']['join'] = array(
// Directly links to users table.
'users' => array(
'left_field' => 'uid',
'field' => 'uid',
),
'node' => array(
'table' => 'users_roles',
'left_field' => 'uid',
'field' => 'uid',
),
'node_revisions' => array(
'table' => 'users',
'left_field' => 'uid',
'field' => 'uid',
),
);

$data['users_roles']['rid'] = array(
'title' => t('Roles'),
'help' => t('Roles that a user belongs to.'),
'field' => array(
'handler' => 'views_handler_field_user_roles',
),
'filter' => array(
'handler' => 'views_handler_filter_user_roles',
'numeric' => TRUE,
),
'argument' => array(
'handler' => 'views_handler_argument_users_roles_rid',
'name table' => 'role',
'name field' => 'name',
'empty name field' => t('No role'),
'numeric' => TRUE,
),
);

Lapino’s picture

Lapino commented

I tested it and changing the above piece of code makes this work. Great find!

merlinofchaos’s picture

merlinofchaos commented
Status: Active » Fixed

Peter: I'd appreciate it if you'd tell me what you change rather than pasting chunks of code; I am uncomfortable pasting large chunks of code without knowing exactly what the changes are, as it makes it very difficult for me to do a proper review. That's one of the main benefits of patches -- they tell exactly what has changed.

Assuming the only thing you changed was the 'table' directive, simply eliminating that actually works. Committed to -dev.

peter_halada’s picture

peter_halada commented

Ok, no problem for next time.

Anonymous’s picture

Anonymous (not verified) commented
Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for two weeks with no activity.