drupal 8, drupal 9 - Views - Add extra condition on join

custom_join.module

<?php

/**
 * @file
 * Primary module hooks for custom_join module.
 */

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


/**
 * Implements hook_views_query_alter().
 */
function custom_join_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  /**
   * The view contains a relationship. 
   * The relationship joins all authors
   * On this example, we want all nodes to show but only show the author if the name is 'admin'
   * A relationship on author and adding the field author name to the view by default does 
   *   a left join (not required) and shows all authors names. 
   * We basically have,
   *  LEFT JOIN users_field_data on node_field_data.uid = users_field_data.uid
   * but want
   *  LEFT JOIN users_field_data on node_field_data.uid = users_field_data.uid and users_field_data.name = "admin"
   **/

  // - https://drupal.stackexchange.com/a/251700
  // Get the alias that the view is using. Not the direct name from the db table
  $table = $query->getTableInfo('users_field_data_node_field_data');
  $table['join']->extra = empty($table['join']->extra) ? [] : $table['join']->extra;
  $table['join']->extra[] = [
    'field' => 'name',
    'value' => 'admin',
    'operator' => '='
  ];
}

views.view.pages.yml

uuid: b07cfe3c-0812-4ad2-a542-a9fe0988f2b9
langcode: en
status: true
dependencies:
  config:
    - node.type.page
  module:
    - node
    - user
id: pages
label: pages
module: views
description: ''
tag: ''
base_table: node_field_data
base_field: nid
display:
  default:
    display_plugin: default
    id: default
    display_title: Master
    position: 0
    display_options:
      access:
        type: perm
        options:
          perm: 'access content'
      cache:
        type: tag
        options: {  }
      query:
        type: views_query
        options:
          disable_sql_rewrite: false
          distinct: false
          replica: false
          query_comment: ''
          query_tags: {  }
      exposed_form:
        type: basic
        options:
          submit_button: Apply
          reset_button: false
          reset_button_label: Reset
          exposed_sorts_label: 'Sort by'
          expose_sort_order: true
          sort_asc_label: Asc
          sort_desc_label: Desc
      pager:
        type: mini
        options:
          items_per_page: 10
          offset: 0
          id: 0
          total_pages: null
          expose:
            items_per_page: false
            items_per_page_label: 'Items per page'
            items_per_page_options: '5, 10, 25, 50'
            items_per_page_options_all: false
            items_per_page_options_all_label: '- All -'
            offset: false
            offset_label: Offset
          tags:
            previous: ‹‹
            next: ››
      style:
        type: table
        options:
          grouping: {  }
          row_class: ''
          default_row_class: true
          override: true
          sticky: false
          caption: ''
          summary: ''
          description: ''
          columns:
            title: title
            title_1: title_1
          info:
            title:
              sortable: false
              default_sort_order: asc
              align: ''
              separator: ''
              empty_column: false
              responsive: ''
            title_1:
              sortable: false
              default_sort_order: asc
              align: ''
              separator: ''
              empty_column: false
              responsive: ''
          default: '-1'
          empty_table: false
      row:
        type: fields
        options:
          inline: {  }
          separator: ''
          hide_empty: false
          default_field_elements: true
      fields:
        title:
          id: title
          table: node_field_data
          field: title
          relationship: none
          group_type: group
          admin_label: ''
          label: 'Node Title'
          exclude: false
          alter:
            alter_text: false
            text: ''
            make_link: false
            path: ''
            absolute: false
            external: false
            replace_spaces: false
            path_case: none
            trim_whitespace: false
            alt: ''
            rel: ''
            link_class: ''
            prefix: ''
            suffix: ''
            target: ''
            nl2br: false
            max_length: 0
            word_boundary: false
            ellipsis: false
            more_link: false
            more_link_text: ''
            more_link_path: ''
            strip_tags: false
            trim: false
            preserve_tags: ''
            html: false
          element_type: ''
          element_class: ''
          element_label_type: ''
          element_label_class: ''
          element_label_colon: true
          element_wrapper_type: ''
          element_wrapper_class: ''
          element_default_classes: true
          empty: ''
          hide_empty: false
          empty_zero: false
          hide_alter_empty: true
          click_sort_column: value
          type: string
          settings:
            link_to_entity: true
          group_column: value
          group_columns: {  }
          group_rows: true
          delta_limit: 0
          delta_offset: 0
          delta_reversed: false
          delta_first_last: false
          multi_type: separator
          separator: ', '
          field_api_classes: false
          entity_type: node
          entity_field: title
          plugin_id: field
        name:
          id: name
          table: users_field_data
          field: name
          relationship: uid
          group_type: group
          admin_label: ''
          label: Name
          exclude: false
          alter:
            alter_text: false
            text: ''
            make_link: false
            path: ''
            absolute: false
            external: false
            replace_spaces: false
            path_case: none
            trim_whitespace: false
            alt: ''
            rel: ''
            link_class: ''
            prefix: ''
            suffix: ''
            target: ''
            nl2br: false
            max_length: 0
            word_boundary: true
            ellipsis: true
            more_link: false
            more_link_text: ''
            more_link_path: ''
            strip_tags: false
            trim: false
            preserve_tags: ''
            html: false
          element_type: ''
          element_class: ''
          element_label_type: ''
          element_label_class: ''
          element_label_colon: true
          element_wrapper_type: ''
          element_wrapper_class: ''
          element_default_classes: true
          empty: ''
          hide_empty: false
          empty_zero: false
          hide_alter_empty: true
          click_sort_column: value
          type: user_name
          settings:
            link_to_entity: true
          group_column: value
          group_columns: {  }
          group_rows: true
          delta_limit: 0
          delta_offset: 0
          delta_reversed: false
          delta_first_last: false
          multi_type: separator
          separator: ', '
          field_api_classes: false
          entity_type: user
          entity_field: name
          plugin_id: field
      filters:
        status:
          value: '1'
          table: node_field_data
          field: status
          plugin_id: boolean
          entity_type: node
          entity_field: status
          id: status
          expose:
            operator: ''
            operator_limit_selection: false
            operator_list: {  }
          group: 1
        type:
          id: type
          table: node_field_data
          field: type
          value:
            page: page
          entity_type: node
          entity_field: type
          plugin_id: bundle
          expose:
            operator_limit_selection: false
            operator_list: {  }
      sorts:
        created:
          id: created
          table: node_field_data
          field: created
          order: DESC
          entity_type: node
          entity_field: created
          plugin_id: date
          relationship: none
          group_type: group
          admin_label: ''
          exposed: false
          expose:
            label: ''
          granularity: second
      header: {  }
      footer: {  }
      empty: {  }
      relationships:
        uid:
          id: uid
          table: node_field_data
          field: uid
          relationship: none
          group_type: group
          admin_label: author
          required: false
          entity_type: node
          entity_field: uid
          plugin_id: standard
      arguments: {  }
      display_extenders: {  }
    cache_metadata:
      max-age: -1
      contexts:
        - 'languages:language_content'
        - 'languages:language_interface'
        - url.query_args
        - 'user.node_grants:view'
        - user.permissions
      tags: {  }
  page_1:
    display_plugin: page
    id: page_1
    display_title: Page
    position: 1
    display_options:
      display_extenders: {  }
      path: page-authors
    cache_metadata:
      max-age: -1
      contexts:
        - 'languages:language_content'
        - 'languages:language_interface'
        - url.query_args
        - 'user.node_grants:view'
        - user.permissions
      tags: {  }