Add ON clause to the left join of drupal views query

Drupal views provide an interactive UI to present almost any data from the database to the user. To add any left join to the query generated by our view, we normally add relationships to the view table. This page illustrates how to Add ON clause to the left join of drupal views query.

Add ON clause to the left join of drupal views query

The Relationships from the above screenshot adds a new join to the from clause. If you don't enable the Require checkbox on the form, the join is added as LEFT JOIN instead of INNER JOIN to the SQL query generated for the view.

Many a times, data isn't presented in the way we need, eg we can add the where clause in the filter section of the view to restrict the rows, but to include all the data from the main table (upon which the view is built), it is required to append the ON clause to the LEFT JOIN built by the relationships.

Add On Clause to the LEFT JOIN of Drupal Views Query.

Below code snippet uses the hook_views_query_alter to add the extra ON clause to the join statement in the view query,

 

function hook_views_query_alter(&$view, &$query) {

 if ($view->name == 'wwdc_status_volunteer_list_3') {
    $query->table_queue['table_name']['join']->extra = [
      [
        'field' => 'field_name',
        'operator' => '=',
        'value' => <value>,
        'numeric' => FALSE,
      ]
    ];
    $query->table_queue['table_name']['join']->extra = [
      [
        'field' => 'field_name2',
        'operator' => '=',
        'value' => <value2>,
      ]
    ];
  }
}