Show if there are 'new' comments on a view of terms?

Question

I have comments on nodes that are categorised by terms. I need to show a view of the terms and if there are any new comments in any of the nodes that term is tagged with.

So if I have the following:

Term1
-NodeA (has new comments)
-NodeB (has new comments) 
Term2
-NodeC (no new comments) 
-NodeD (no new comments) 
Term3
-NodeE (no new comments) 
-NodeF (has new comments)
Term4

I need my view to look like this:

Term1 – new comments!
Term2
Term3 – new comments!
Term4

First I tried making a view of comments to include the new comments field and using relationships to show the terms. However if a term has no nodes or comments (Term4 in my example) I need the term to still be displayed.

So I then tried making a view of the terms. I added the relationships 'Taxonomy term: Content with term' and the field 'Content: New comments' which uses this relationship. This does show the number of new comments as expected. It gives me duplicated results (which I may be able to fix with grouping fields) but unexpectedly it also hides the terms that have no comments or nodes.

Am I going about this the right way and if so how can I stop the 'empty' terms being hidden?

UPDATE Ive updated D core and a few modules that had new versions (but not views). The view works fine with the relationship of 'Configure Relationship: Taxonomy term: Content with term' when the field is 'Content: Title (Title)'. However when I add the field 'Content: New comments' then terms that have no comments are hidden.

UPDATE Ive tried the following in a views field:

if(intval($data->node_new_comments) > 0) {
  if(!array_key_exists($data->tid, $static)) {
    print "new comments !";
    $static[$data->tid] = 1;
  }
} else {
  print "here you do whatever you want when there's no new comment";
}

UPDATE - by 'new comments' I mean comments that the logged in user hasnt read. This is a standard field in views. I need this rather than an arbitrary date cut off. Sorry for any confusion.

Answer 1

This works for me. I have a term view that shows the term name field and my code adds the new comment flag after the term name.

The code goes in a custom module.

You need to change to VIEW_NAME to your view name and the TAXONOMY_FIELD_NAME to the name of your field (like field_tags for example).

/**
 * Implements hook_views_pre_render().
 */
function MYMODULE_views_pre_render(&$view) {
  global $user;
  if ($view->name == 'VIEW_NAME' && $user->uid) {
    // Get all the tids of the views results.
    $tids = array();
    foreach ($view->result as $result) {
      $tids[] = $result->tid;
    }

    // Get all the tids that have recent comments.
    // Only return terms that relate to nodes that have not been viewed by
    // the current user since the last comment.
    $query = db_query("SELECT t.TAXONOMY_FIELD_NAME_tid
                         FROM {node_comment_statistics} c
              LEFT OUTER JOIN {history} h ON c.nid = h.nid AND h.uid = :uid
                   INNER JOIN {field_data_TAXONOMY_FIELD_NAME} t ON t.entity_id = c.nid
                        WHERE c.comment_count > 0
                          AND t.TAXONOMY_FIELD_NAME_tid IN (:tids)
                          AND (h.timestamp IS NULL OR h.timestamp < c.last_comment_timestamp)",
               array(':uid' => $user->uid, ':tids' => $tids));
    $commented_terms = $query->fetchCol();

    // Loop through the results and add the new comments flag where necessary.
    foreach ($view->result as &$result) {
      if (in_array($result->tid, $commented_terms)) {
        $result->taxonomy_term_data_name .= ' new comments!';
      }
    }
  }
}

Answer 2

Adding the 'Content: New comments' field adds an INNER JOIN to the node_comment_statistics table, which can potentially be the source of the problems you're experiencing. (For example, if there's no entries for NodeC, NodeD and NodeE in node_comment_statistics for some reason, the terms will not be shown due to the JOIN being an INNER one.)

Does rebuilding node_comment_statistics help?

Answer 3

Here's a sketch of an answer that'll get you most of the way there, I think. Start by creating a view of nodes, then add the taxonomy and the latest comment time as fields.

Now turn on aggregation for the view, and set the aggregation for the taxonomy field to be "group results together", on the column "tid".

Change the aggregation setting for the comment time field to be "Maximum".

You should now have a view which lists all your taxonomy terms (at least those that have content) with the last comment date. The final step will be to process the comment timestamp in a views hook or theming to make it display as you require, which should be relatively simple.

Answer 4

You have to make another query first, to retrieve the terms which have nodes with new contents.

I've been helped by this thread.

As I told you, I put all this in php fields ; if you have the time, it'll be better to put it in a module, in a views_query_alter or else.

1. Add a php field, and in the setup code, go grab the tids' list of terms which have new content :

   $query = db_select('node_comment_statistics', 'c')
     ->condition('c.last_comment_timestamp', 'history_user.timestamp', '>');
   $query->join('field_data_YOURTERMREFERENCEFIELD', 't', 't.entity_id=c.nid');
   $query->fields('t', array('YOURTERMREFERENCEFIELD_tid'))
     ->groupBy('t.YOURTERMREFERENCEFIELD_tid');
   
   $static = $query->execute()->fetchCol();
   

2. Now in the output code, check that your term is in this array :

   if (in_array($data->tid, $static)) {
     print 'new comments !';
   }
   

This is the lightest I see, even if the query can be heavy if you have a large amount of nodes and comments.

EDIT: I have to recall that putting php in the sql with views php is NOT a good practice, I invite you to put all that in a custom module in hook_views_query_alter as soon as you can.