[SOLVED] How to Add Join in Views Query Alter?

Views query alter is used to edit the query in views that we created using Drupal. In cases where we cannot add fields in views, we can use hook_views_query_alter to change the query in views.

Example of Views Query Alter by using Join.

function MY_MODULE_views_query_alter(&$view, &$query) {
  
    // Filter based on todays date - added for making conditions.
    $todays_start_time = mktime(0, 0, 0, date("m")  , date("d"), date("Y"));
    $todays_end_time = mktime(0, 0, 0, date("m")  , date("d")+1, date("Y"));
    
    // Write the join conditions.
    $join = new views_join();
    $join->table = 'field_data_field_order_selected_date';
    $join->field = 'entity_id';
    $join->left_table = 'commerce_order';
    $join->left_field = 'order_number';
    $join->type = 'LEFT';
    
    // Append to current query.
    $query->table_queue['field_data_field_order_selected_date__commerce_order'] = array (
      'table' => 'field_data_field_order_selected_date',
      'alias' => 'field_data_field_order_selected_date',
      'relationship' => 'commerce_order',
      'join' => $join,
    );
    
    // Add extra where conditions.
    $query->where[] = array(
      'conditions' => array(
       array(
        'field' => 'field_data_field_order_selected_date.field_order_selected_date_value',
        'value' => $todays_start_time,
        'operator' => '>',
       ),
      ),
      'type' => 'AND',
    );
    $query->where[] = array(
      'conditions' => array(
       array(
        'field' => 'field_data_field_order_selected_date.field_order_selected_date_value',
        'value' => $todays_end_time,
        'operator' => '<',
       ),
      ),
      'type' => 'AND',
    );
}

Here we added join using class views_join in Drupal. It's a built-in function to represent a join and to create the SQL necessary to implement the join.