duplicate count under a user role [#800482]

First of all, big thanks for this great module! Very useful!

I would like to request help with an issue and ran into...

I created a new view using the tutorial (http://drupal.org/node/389230) (*please, see the attached screenshots).
I don't have any issues displaying the results. The results do appear to be correct under the admin account.

e.g.
New York (25)
Vancouver (20)
Philadelphia (15)
Edmonton (10)
Miami (5)
etc....

here's the query:

SELECT COUNT(node.nid) AS nid,
   term_data.name AS term_data_name,
   term_data.vid AS term_data_vid,
   term_data.tid AS term_data_tid,
   vocabulary.name AS vocabulary_name
 FROM node node 
 LEFT JOIN term_node term_node ON node.vid = term_node.vid
 LEFT JOIN term_data term_data ON term_node.tid = term_data.tid
 LEFT JOIN vocabulary vocabulary ON term_data.vid = vocabulary.vid
 WHERE term_data.vid in ('5')
 GROUP BY term_data_name, vocabulary_name, term_data_name, term_data_vid, term_data_tid, vocabulary_name
  ORDER BY nid DESC

when I preview the page (under my admin account) everything seems to be working well...
BUT... if I view the same page under my "editor user role" the results are incorrect... the nid count seems to be duplicate for all terms.... more specifically I get

New York (50)
Vancouver (40)
Philadelphia (30)
Edmonton (20)
Miami (10)
etc....

despite the fact that there are only e.g. 25 terms for New York, 20 terms for Vancouver etc...

I tried to use "Distinct" under Basic settings but if I do... I get the following error message:

user warning: Can't group on 'nid' query: SELECT DISTINCT COUNT(DISTINCT(node.nid)) AS nid, term_data.name AS term_data_name, term_data.vid AS term_data_vid, term_data.tid AS term_data_tid, vocabulary.name AS vocabulary_name FROM node node LEFT JOIN term_node term_node ON node.vid = term_node.vid LEFT JOIN term_data term_data ON term_node.tid = term_data.tid LEFT JOIN vocabulary vocabulary ON term_data.vid = vocabulary.vid WHERE term_data.vid in ('5') GROUP BY term_data_name, vocabulary_name, nid ORDER BY nid DESC in /var/www/vhosts/b4d.ca/httpdocs/sites/all/modules/views/includes/view.inc on line 775.

I flushed all cache, created multiple views from scratch, ran updates, ran crons... nothing really fixed this...

I'm using Views 6.x-2.10 with this module.

Any help / suggestions would be greatly appreciated.

Thank you!

Comment	File	Size	Author
	sql-view-2.png	125.31 KB	vasheck
	sql-view-1.jpg	106.62 KB	vasheck

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

vasheck CreditAttribution: vasheck commented 16 May 2010 at 20:55

if this is going to be any help... here's my view:

$view = new view;
$view->name = 'tester_tester';
$view->description = 'tester_tester';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->override_option('fields', array(
  'name' => array(
    'label' => '',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'link_class' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'target' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
      'strip_tags' => 0,
    ),
    'empty' => '',
    'hide_empty' => 0,
    'empty_zero' => 0,
    'link_to_taxonomy' => 1,
    'exclude' => 0,
    'id' => 'name',
    'table' => 'term_data',
    'field' => 'name',
    'relationship' => 'none',
  ),
  'name_1' => array(
    'label' => 'Vocabulary name',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'link_class' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'target' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
      'strip_tags' => 0,
    ),
    'empty' => '',
    'hide_empty' => 0,
    'empty_zero' => 0,
    'exclude' => 1,
    'id' => 'name_1',
    'table' => 'vocabulary',
    'field' => 'name',
    'relationship' => 'none',
  ),
  'nid' => array(
    'label' => '',
    'alter' => array(
      'alter_text' => 1,
      'text' => '([nid])',
      'make_link' => 0,
      'path' => '',
      'link_class' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'target' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
      'strip_tags' => 0,
    ),
    'empty' => '',
    'hide_empty' => 0,
    'empty_zero' => 0,
    'link_to_node' => 0,
    'exclude' => 0,
    'id' => 'nid',
    'table' => 'node',
    'field' => 'nid',
    'relationship' => 'none',
  ),
  'views_sql_groupedfields' => array(
    'label' => 'Group By Fields',
    'alter' => array(
      'alter_text' => FALSE,
      'text' => '',
      'make_link' => FALSE,
      'path' => '',
      'alt' => '',
      'link_class' => '',
      'prefix' => '',
      'suffix' => '',
      'target' => '',
      'trim' => FALSE,
      'max_length' => '',
      'word_boundary' => TRUE,
      'ellipsis' => TRUE,
      'strip_tags' => FALSE,
      'html' => FALSE,
    ),
    'empty' => '',
    'hide_empty' => 0,
    'empty_zero' => 0,
    'exclude' => '1',
    'id' => 'views_sql_groupedfields',
    'table' => 'views_groupby',
    'field' => 'views_sql_groupedfields',
    'relationship' => 'none',
    'views_groupby_fields_to_group' => array(
      'name' => 'name',
      'name_1' => 'name_1',
    ),
    'views_groupby_sql_function' => 'count',
    'views_groupby_fields_to_aggregate' => array(
      'nid' => 'nid',
    ),
    'views_groupby_field_sortby' => 'nid',
    'views_groupby_sortby_direction' => 'desc',
  ),
));
$handler->override_option('filters', array(
  'vid' => array(
    'operator' => 'in',
    'value' => array(
      '5' => '5',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'vid',
    'table' => 'term_data',
    'field' => 'vid',
    'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
  'type' => 'role',
  'role' => array(
    '3' => 3,
  ),
));
$handler->override_option('cache', array(
  'type' => 'none',
));
$handler->override_option('items_per_page', 0);
$handler->override_option('distinct', 0);
$handler->override_option('row_options', array(
  'inline' => array(
    'name' => 'name',
    'nid' => 'nid',
  ),
  'separator' => '',
  'hide_empty' => 0,
));
$handler = $view->new_display('page', 'Page', 'page_1');
$handler->override_option('path', 'tester_tester');
$handler->override_option('menu', array(
  'type' => 'none',
  'title' => '',
  'description' => '',
  'weight' => 0,
  'name' => 'navigation',
));
$handler->override_option('tab_options', array(
  'type' => 'none',
  'title' => '',
  'description' => '',
  'weight' => 0,
  'name' => 'navigation',
));

Thanks!