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

November 17, 2017 - 17:27

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.

Get Drupal updates straight to your inbox

To prevent automated spam submissions leave this field empty.

Post your comments / questions