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
Comment #1
vgulla CreditAttribution: vgulla commentedWas this issue ever resolved. Please respond
Comment #2
jstollerTo 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.