How to create a view of nodes *without* comments from currently logged in user

Question

I'm trying to create a view that lists nodes that don't have any comments from the currently logged in user. The nodes may have comments from other users, but if there is no comment from the current user then it should be listed.

I can show nodes with comments from the currently logged in user but not nodes without comments.

Almost like a "to-do" list of nodes that need to be commented on by the current user.

I'm a fairly advance views user (I've tried all I know by way of contextual filters, relationships, different content approaches like listing comments or users and connecting them through relationships... ) but all to no avail.

Anyone else come across this or have ideas where I can make more progress?

Answer 1

REVISED ANSWER

This can be accomplished in two step process.

1. Create a very plain view to only show node titles. Do not filter it except by type if you wish, and do not place any contextual filters on it. For this answer I created a view called 'not_commented' with a page display of 'page_1'.

2. Create a custom module. We will use it to alter the views query. For this answer I created a module called 'custom'.

Add this code to custom.module:

function custom_views_api(){
  return array(
    'api' => 3,
    'path' => drupal_get_path('module', 'custom') . '/includes/views',
  );
}

Now create a folder 'includes' in the module folder, and then a folder 'views' inside the includes folder so that the folder structure is : sites/all/modules/custom/includes/views

Create a file custom.view.inc within folder views. Place this code in the file:

<?php

function custom_views_query_alter(&$view, &$query){
  if ($view->name == "not_commented" && $view->current_display == "page_1"){
    // get all nodes commented on by this author
    $sql = "SELECT n.nid, COUNT(n.nid) " .
      "FROM {node} n LEFT JOIN {comment} c ON n.nid = c.nid " .
      "WHERE (c.uid = :uid AND n.status = '1') GROUP BY n.nid";
    global $user;
    $query_2_result = db_query($sql, array(':uid' => $user->uid))->fetchAllKeyed(0,0);


    $query->where[1]['conditions'][] = array(
      'field' => 'node.nid',
      'value' => $query_2_result,
      'operator' => 'not in',
    );
  }
}

Clear you cache.

Please replace module and views/display names etc. as applicable. If there are any problems please inspect the $query object to see if you have the right where array key etc.