How can I alter a query adding an OR clause for a relational field?

Question

I have an "Event" node type with a number of fields: title, date, location, description, etc.

Because a number of events are similar, we added a "Parent event" entity reference field. Child events inherit fields (e.g. location, description) from the parent, if set.

We have a block that displays all of the events for a location, using a contextual filter. The problem is that the child events have an empty location – it is inherited from the parent event when we render the event page.

How can I adjust the view query to select all events with either the contextual location ID, or with a parent that has the location ID?

Answer 1

My solution:

• Add a relationship for content referenced from the entity reference field.
• Add a contextual filter for the entity reference field, using the content ID from the URL as the value. This includes events that have the location set directly.
• Add a contextual filter for the entity reference field, using the content ID from the URL as the value, using the relationship. This includes events are related to an event that has the location set.

Unfortunately, the contextual filter WHERE group uses AND by default, so no results are returned, so we have to change the type in the WHERE clause.

use Drupal\views\Plugin\views\query\Sql;
use Drupal\views\ViewExecutable;

/**
 * Implements hook_views_query_alter().
 */
function my_module_views_query_alter(ViewExecutable $view, Sql $query) {
  if ($view->id() === 'events') {
    if ($view->current_display === 'block_2') {
      $query->where[0]['type'] = 'OR';
    }
  }
}

If there are more contextual filters, you have to create a new OR where group with the fields, and remove them from group 0.

$group0 = &$query->where[0];

// Create a new OR group.
$group_id = $query->setWhereGroup('OR');

// Copy the conditions to the new where group.
$query->addWhereExpression(
    $group_id,
    $group0['conditions'][1]['field'],
    $group0['conditions'][1]['value']
);
$query->addWhereExpression(
    $group_id,
    $group0['conditions'][2]['field'],
    $group0['conditions'][2]['value']
);

// Remove from group 0
unset($group0['conditions'][1]);
unset($group0['conditions'][2]);