2

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

4
  • Does this help? drupal.stackexchange.com/a/251700/48114
    – sonfd
    Jul 3, 2021 at 21:28
  • Almost, but not. It shows how to add an 'ON' condition, but the condition is for a fixed value, where I the condition to be on another table column.
    – Jaypan
    Jul 3, 2021 at 22:48
  • Can you use some of the other options listed here: api.drupal.org/api/drupal/…
    – sonfd
    Jul 3, 2021 at 23:24
  • Thank you for the additional information. I played with it for a while, but I don't see any combination that allows me to get the right tables and columns referenced in the query, particularly as value is required, but I'm not working with a value, I'm working with a column name.
    – Jaypan
    Jul 4, 2021 at 1:23

1 Answer 1

3

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],
    ];
  }
}

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.