Drupal views hook_views_query_alter move a where clause restriction to a join

view_query_alter_example.php

/**
 * Implements hook_views_query_alter().
 *
 * This is a view of users and the view has a relationship to node and a contextual filter.
 * There is an entity reference field on the user pointing to the node called field_node_entity_reference.
 *
 * By placing the restiction on the join and not requiring the relationship means we can additional exposed filters
 * such as where node.nid is empty (users who do not link to the node).
 */
function mymodule_views_query_alter(&$view, &$query) {
    if ($view->name !== 'my_view') {
      return;
    }

    $cond = NULL;
  foreach ($query->where as $whereCondKey => $whereCond) {
    if (isset($whereCond['conditions'][0]['field']) && trim($whereCond['conditions'][0]['field']) == 'node_field_data_field_node_entity_reference.nid = :node_nid') {
      $cond = $whereCond;
      unset($query->where[$whereCondKey]);
    }
  }

  if (!isset($query->table_queue['field_data_field_node_entity_reference']['join']->extra)) {
    $query->table_queue['field_data_field_node_entity_reference']['join']->extra = array();
  }

  if (isset($cond)) {
    $query->table_queue['field_data_field_node_entity_reference']['join']->extra[] = array(
      'field' => 'field_node_entity_reference_target_id',
      'value' => $cond['conditions'][0]['value'][':node_nid']
    );
  }

  if (!isset($query->table_queue['node_field_data_field_node_entity_reference']['join']->extra)) {
    $query->table_queue['node_field_data_field_node_entity_reference']['join']->extra = array();
  }

  if (isset($cond)) {
    $query->table_queue['node_field_data_field_node_entity_reference']['join']->extra[] = array(
      'field' => 'nid',
      'value' => $cond['conditions'][0]['value'][':node_nid']
    );
  }
}