I am trying to create a View that counts the number of nodes of each content type which contain a given search term. The view uses the Search: Search Terms filter, which is exposed to the user. I included three fields: Node: Type, Node: NID and SQL Aggregation: Group By Fields. I set the SQL Aggregation field to group on Node: Type and aggregate Node: NID.

If I don't enter a search term, then the view works perfectly, giving me a count of nodes for each content type. The SQL query in this instance is:

SELECT COUNT(node.nid) AS nid,
   node.type AS node_type
 FROM node node 
  GROUP BY node_type
  ORDER BY node_type ASC

But if I enter a search term, the view fails to aggregate properly, listing each node in a separate row with a count of "1". The SQL query in this instance is:

SELECT COUNT(node.nid) AS nid,
   SUM(search_index.score * search_total.count) AS score,
   node.type AS node_type
 FROM node node 
 LEFT JOIN search_index search_index ON node.nid = search_index.sid
 LEFT JOIN search_total search_total ON search_index.word = search_total.word
 WHERE (search_index.word = 'money') AND (search_index.type = 'node')
 GROUP BY search_index.sid, node_type
 HAVING COUNT(*) >= 1
 ORDER BY node_type ASC

Here's my full view export:

$view = new view;
$view->name = 'search_term';
$view->description = 'Count the number of times a term is referenced on the site.';
$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(
  'type' => 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_node' => 0,
    'machine_name' => 0,
    'exclude' => 0,
    'id' => 'type',
    'table' => 'node',
    'field' => 'type',
    'relationship' => 'none',
  ),
  'nid' => 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_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',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
    'views_groupby_fields_to_group' => array(
      'type' => 'type',
    ),
    'views_groupby_sql_function' => 'count',
    'views_groupby_fields_to_aggregate' => array(
      'nid' => 'nid',
    ),
    'views_groupby_field_sortby' => 'type',
    'views_groupby_sortby_direction' => 'asc',
  ),
));
$handler->override_option('filters', array(
  'keys' => array(
    'operator' => 'required',
    'value' => '',
    'group' => '0',
    'exposed' => TRUE,
    'expose' => array(
      'use_operator' => 0,
      'operator' => 'keys_op',
      'identifier' => 'keys',
      'label' => 'Term to search for',
      'optional' => 1,
      'remember' => 0,
    ),
    'id' => 'keys',
    'table' => 'search_index',
    'field' => 'keys',
    'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
));
$handler->override_option('cache', array(
  'type' => 'none',
));
$handler->override_option('title', 'Search Term Count');
$handler->override_option('items_per_page', 0);
$handler->override_option('use_pager', '0');
$handler->override_option('style_options', array(
  'grouping' => '',
));
$handler->override_option('row_options', array(
  'inline' => array(
    'type' => 'type',
    'nid' => 'nid',
  ),
  'separator' => ': ',
  'hide_empty' => 0,
));
$handler = $view->new_display('page', 'Page', 'page_1');
$handler->override_option('path', 'term-count');
$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',
));

Comments

vgulla’s picture

Was this issue ever resolved. Please respond

jstoller’s picture

To be honest, I'm not entirely sure if/how I resolved this issue. I'll try to take a look back at that old site and check, when I have a free moment.