Eliminate results from a relationship (add an ON condition to JOIN)

Question

I need to add an ON condition to a views JOIN. I cannot use a contextual filter, as it will filter out results altogether, and I need to get a result regardless (there are other joins). So I need to ensure the filter happens on the JOIN, and not as a WHERE condition.

To be more specific, I want to show a value when no relationship exists. The relationship is from one NODE type to another NODE of the same type, and I don't want the current node retrieved. So the ON condition should be AND join_table.id != original_table.id.

I'm new to altering views queries. Can anyone provide assistance either on how to add an ON condition to a Views query either through the admin UI (if possible) or through a query alter?

Thank you

Answer 1

Thanks to help from sonfd above, I was able to come up with a solution of sorts. I was not able to compares the two columns as I was trying to, but I was able to use $view->args to come up with the result I wanted.

To answer the question on how to add an additional ON condition to JOIN, you can do the following:

function HOOK_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  if ($view->id() == VIEW_ID && $view->current_display === DISPLAY_ID) {
    $table = $query->getTableInfo(TABLE_NME);
    $table['join']->extra = empty($table['join']->extra) ? [] : $table['join']->extra;
    $table['join']->extra[] = [
      'field' => FIELD_NAME,
      'operator' => '!=',
      'value' => VALUE, // In my case $view->args[0],
    ];
  }
}