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 <== LEFT JOIN catalogue <== 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) > 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?

  • How do you join the entities? I guess with entity-reference. If views you have Relationship settings where you can add reference by fields
    – milkovsky
    Aug 20 '14 at 23:29
  • Yes, I know about the Relationship settings. The problem is that the join attempts to relate catalogue_ranking nodes to the item nodes, not to the catalogue nodes. If I use a relationship inside a relationship, it gets closer but still matches on the wrong fields.
    – GuruJ
    Aug 21 '14 at 0:35
  • filter on item. Add relationship a bridge to the Content entity that is referencing Content via the entity reference field from catalogue to item. Add relationship a bridge to the Content entity that is referencing Content via the entity reference field from ranking to catalogue, and set previous relationship as relationship. Add sort on rank field and set relationship to last relationship added. That is if I read your relationships correctly. Aug 21 '14 at 4:23
  • Hi J, yes, that is exactly what I would have thought should work, but it doesn't :( Although I should caveat that by saying that I am using a Node Reference field instead of an Entity Reference field because ER is way too slow when doing lookups. Could that make a difference?
    – GuruJ
    Aug 21 '14 at 6:15

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


 * Implements hook_views_data_alter().
function custom_log_views_data_alter(&$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.'),
enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.