Views Group By module - how to use it

Drupal 6 only

This is a tutorial of creating aggregated SQL queries (the ones with a "GROUP BY" clause) in Views2. At this time, to achieve this task you must install the Views GroupBy contributed module.

Top Terms View

Views is just great most of the time, but sometimes, for complex queries, it falls short. Not all SQL queries can be represented even with a query builder as sophisticated as Views2. One of the things you used to not be able to build was an aggregate query. It's a query when you want to group fields by some criteria and run a function on the group. For instance: group nodes by term name and count how many nodes are in each group. This is a query for Term Frequency.

Real-life example: List top 10 most frequent terms applied to a specific node type and the number of nodes for each term.

Building such view used to be impossible with Views2, unless you wrote some glue code. Not anymore. Following is a step-by-step configuration instructions for building term frequency view. Similar aggregated queries can be built in almost identical way.

1. Create a new Node View
2. Add following fields:
   • Taxonomy: Term Term
   • Taxonomy: Vocabulary Name
   • Node: Nid Nid (and rename its label to Nid Count)
   • SQL Aggregation: Group By Fields Group By Fields (must be last field!)
3. Add a field named "SQL Aggregation: Group By Fields" and configure its settings as follows:
   • Fields to Group On = 'Taxonomy: Term' and 'Taxonomy: Vocabulary Name' (both fields)
   • SQL Aggregation Function = Count
   • Fields to Aggregate with the SQL function = Node: Nid
   • Field to Sort Resultset On (after SQL Function is applied) = Node: Nid
   • Sorting Direction: Descending
4. Add a filtering criteria for the node type if you need to.

Which will produce the following SQL:

SELECT COUNT(node.nid) AS nid,
   term_data.name AS term_data_name,
   vocabulary.name AS vocabulary_name,
   'term_frequency:page_1' AS view_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
  GROUP BY term_data_name, vocabulary_name, term_data_name, vocabulary_name, view_name
  ORDER BY nid DESC

which is almost an ideal query for the task, granted that we are using a cross-database query builder and it has some natural limitations.

The output of the given query may look something like when rendered with HTML List display plugin:

Term: gaza
Nid Count: 272
Vocabulary: Yahoo Terms
Term: israel
Nid Count: 126
Vocabulary: Yahoo Terms
Term: news articles
Nid Count: 88
Vocabulary: Yahoo Terms
Term: new york times
Nid Count: 50
Vocabulary: Yahoo Terms
Term: hamas
Nid Count: 47
Vocabulary: Yahoo Terms
Term: humanitarian crisis
Nid Count: 44
Vocabulary: Yahoo Terms
Term: financial crisis
Nid Count: 43
Vocabulary: Yahoo Terms

and of course you can theme the result-set any way you want using usual Views2 theming techniques.

Most Active FeedAPI Feeds View

Brief description of the approach:

1. Let's assume your feed items have node type "feeditem"
2. Create a view filtered by "feeditem" node type
3. Add a FeedAPI relationship and add "Parent Feed's nid" and its title as one of the fields
4. Group by parent feed's title and nid
5. aggregate (count) by the feeditem's nid

One more thing...

You can also get a nice graphical chart (bar, pie, 3d... you name it) if you install and configure another Views extension, the Views Charts module.

Pretty cool?