0

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).

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.

2
  • I could end up with a few top users who have participated in up to a few thousand discussions. What would the performance of this approach be like for these users?
    – Jonathan
    Nov 23 '16 at 11:49
  • That case needs performance benchmarking. I've added another approach to the answer.
    – ya.teck
    Nov 23 '16 at 13:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.