How to change the order in which Views SQL joins are executed?

Question

Views creates certain JOINs behind the scenes, e.g. for fields or filders added via the UI.

I have a view with such an implicit join based on an entityreference field. In order to get the desired results I need this join to be the first one, but Views happens to place it as the last JOIN (other joins in the query come from other fields and Views relationships).

Can I change the order of the joins, while keeping all fields and filters configured via the Views UI?

As a workaround I can explicitly add a relationship based on the entityreference field in question and order it as the first one. This works, but it adds an extra join that I don't need, which has a performance impact.

Answer 1

You are looking for the following: https://api.drupal.org/api/views/views.api.php/function/hook_views_query_alter/7 It will work roughly as follows: You will find that there is a clauses array in your query object among others that will contains your restraints. Remove the particular element, and insert it into the correct order in the array. They will be executed 0 through N

Answer 2

Following lexicant's advice to just re-order the array, I implemented it as follows. I found explicitly ordering the array keys easier than trying to remove and re-insert an element.

<?php
/**
 * Implements hook_views_query_alter
 */
function MYMODULE_views_query_alter(&$view, &$query) {
  if ($view->name == 'my_view') {
    // Explicitly order all existing table_queue array elements
    $table_queue_sorted_keys = array(
      'node',
      'field_data_field_example2',
      'field_data_field_example1',
      'node_field_data_field_example1',
      // ...
      // Add all tables used by the view. You can inspect the query
      // object by enabling the Devel module and adding `dsm($query);`
      // in your hook_views_query_alter
    );
    $table_queue_sorted = array();
    foreach ( $table_queue_sorted_keys as $key ) {
      $table_queue_sorted[$key] = $query->table_queue[$key];
    }
    $query->table_queue = $table_queue_sorted;
  }
}