Filter a view by a value being absent from a multiple value field

Question

Given a field "field-participants" on a node type "discussion" that references an unlimited number of users:

How can I produce a view that shows all discussions that do NOT have the current user listed in their field_participants?

The problem comes from the fact that views creates a separate row of results for each reference in field_participants in any given discussion.

I can use 'Distinct' or Aggregation to get rid of duplicate rows, but while this would work to include results (showing discussions that listed the current user as a participant) it doesn't work to exclude results.

This is because a contextual filter on the current user with "excludes" checked only excludes the one of these rows that matches the current user. It doesn't exclude all the rows from that discussion. Therefore I get false positives: the view shows all discussions that have someone other than the current user listed as a participant.

Right now I'm thinking that I either need to figure out some arcane HAVING clause and add it to the query programmatically, or try to use the pre-alpha Flag module (setting a per-user flag and filtering by that instead of field_participants).

Answer 1

You could use hook_views_query_alter() to modify SQL query.

/**
 * Implements hook_views_query_alter().
 */
function example_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  if ($view->id() == 'discussions') {
    // Exclude discussions the current logged user has participated in.
    $uid = Drupal::currentUser()->id();
    $nids = Drupal::entityQuery('node')
      ->condition('type', 'discussion')
      ->condition('field_participants', $uid)
      ->execute();
    $query->addWhere(0, 'nid', $nids, 'NOT IN');
  }
}

In the example above we are getting ID's of excluded nodes using entity field query and then them to NOT IN condtion.

The resulting SQL query will be as follows:

SELECT node_field_data.created AS node_field_data_created, node_field_data.nid AS nid
FROM 
{node_field_data} node_field_data
WHERE (nid NOT IN ('1', '2', '3')) AND (node_field_data.type IN ('discussion'))
ORDER BY node_field_data_created DESC

Alternative way would be using JOIN with extra conditions:

/**
 * Implements hook_views_query_alter().
 */
function example_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  if ($view->id() == 'discussions') {
    // Exclude discussions the current logged user has participated in.
    $uid = Drupal::currentUser()->id();  
    $definition = [
      'table' => 'node__field_participants',
      'field' => 'entity_id',
      'left_table' => 'node_field_data',
      'left_field' => 'nid',
      'extra' => [
        0 => [
          'field' => 'field_participants_target_id',
          'value' => $uid,
        ],
      ],
    ];
    $join = Drupal::service('plugin.manager.views.join')->createInstance('standard', $definition);
    $query->addRelationship('participants', $join, 'node');
    $query->addWhere(0, 'field_participants_target_id', NULL, 'IS NULL');
  }
}

The generated SQL query:

SELECT node_field_data.created AS node_field_data_created, node_field_data.nid AS nid
FROM 
{node_field_data} node_field_data
LEFT JOIN {node__field_participants} participants ON node_field_data.nid = participants.entity_id AND participants.field_participants_target_id = '1'
WHERE (field_participants_target_id IS NULL) AND (node_field_data.type IN ('discussion'))
ORDER BY node_field_data_created DESC

The view itself becomes quite simple. No need of contextual argument and aggregation.