How to join three entities in a view? I have a Drupal schema where I want a view to display node 'item' results based on fields from an associated 'catalogue' node. Each associated 'catalogue' has a 'catalogue_ranking' node which is used to order 'item' results.  So the join I am looking for is:

items &lt;== LEFT JOIN catalogue &lt;== LEFT JOIN catalogue_ranking

I can manually construct the SQL I need to produce the results as follows:

SELECT node.nid, substring(node.title, 1, 20) AS title, field_data_field_catalogue.field_catalogue_nid, 
       node_catalogue.nid AS cat_nid, substring(node_catalogue.title, 1, 20) AS cat_title,
       field_data_field_ref_catalogue.field_ref_catalogue_nid, node_cat_ranking.nid AS rank_nid, field_data_field_priority.field_priority_value 
  FROM node node LEFT JOIN field_data_field_catalogue field_data_field_catalogue ON node.nid = field_data_field_catalogue.entity_id 
                 LEFT JOIN node node_catalogue ON node_catalogue.nid = field_data_field_catalogue.field_catalogue_nid 
                 LEFT JOIN field_data_field_ref_catalogue field_data_field_ref_catalogue ON node_catalogue.nid = field_data_field_ref_catalogue.field_ref_catalogue_nid 
                 LEFT JOIN node node_cat_ranking ON node_cat_ranking.nid=field_data_field_ref_catalogue.entity_id 
                 LEFT JOIN field_data_field_priority field_data_field_priority ON node_cat_ranking.nid = field_data_field_priority.entity_id
  WHERE LENGTH(node.title) &gt; 1 ORDER BY IFNULL(field_data_field_priority.field_priority_value, 0) DESC LIMIT 5;


However, I've been going bananas trying to figure out the exact forward or reverse relationships needed to produce the same results using the Drupal view UI. The "Priority" field remains stubbornly blank no matter what combinations I try! What am I doing wrong?
You can implement custom relationship using hook_views_data_alter() and define exactly in this hook which fields to use for JOIN - https://api.drupal.org/api/views/views.api.php/function/hook_views_data_alter/7

Example:

/**
 * Implements hook_views_data_alter().
 */
function custom_log_views_data_alter(&amp;$data) {
  // Expose uid as a relationship to users.
  $data['custom_log']['uid'] = array(
    'title' => t('User information.'),
    'help' => t('Information related to user who triggered the event.'),
    'relationship' => array(
      'base' => 'users',
      'base field' => 'uid',
      'relationship field' => 'uid',
      'handler' => 'views_handler_relationship',
      'label' => t('User information.'),
    ),
  );
}