Specifying table join using multiple field keys in hook_views_data [#810932]

I have two tables that I have specified as join for as follows in hook_views_data:

  $data['market_data_summary']['table'] = array(
    'group' => t('Market Data'),
    'join'  => array(
      'market_data' => array(
        'left_field' => 'mmid',
        'field'      => 'mmid',
      ),
    ),
  );

My problem is that these two tables actually have a multiple-column key. How can I specify more than one key association? Currently I am joining ON the mmid field in both tables -- but ideally I'd like to be able to join ON mmid AND pid AND cid (for example).

Comments

Comment #1

dawehner

German

CreditAttribution: dawehner commented 27 May 2010 at 15:19

See this comment

  $data['node']['table']['join'] = array(
    // this explains how the 'node' table (named in the line above)
    // links toward the node_revision table.
    'node_revision' => array(
      'handler' => 'views_join', // this is actually optional
      'left_table' => 'node_revision', // Because this is a direct link it could be left out.
      'left_field' => 'nid',
      'field' => 'nid',
      // also supported:
      // 'type' => 'INNER',
      // 'extra' => array(array('field' => 'fieldname', 'value' => 'value', 'operator' => '='))
      //   Unfortunately, you can't specify other tables here, but you can construct
      //   alternative joins in the handlers that can do that.
      // 'table' => 'the actual name of this table in the database',
     ),
  );

I hope it helps you.

Comment #2

ISPTraderChris CreditAttribution: ISPTraderChris commented 27 May 2010 at 15:43

The following works in a very simple view -- this makes use of the 'extra' parameter.

  $data['market_data_sources']['table'] = array(
    'group' => t('Market Data'),
    'join'  => array(
      'market_data' => array(
        'left_field' => 'source_id',
        'field'      => 'source_id',
        'extra'      => array(
          array(
            'field'    => 'source_id',
            'value'    => 'market_data.source_id',
            'operator' => '=',
            'numeric'  => true,
          ),
        )
      ),
    ),
  );

My concern with this approach is that I have to manually alias one of the tables (the table with the 'value') -- and I have a feeling this will break down when the view gets more complex. Is this still the best approach? Is there some way to force views to provide the alias it is using?

Comment #3

merlinofchaos CreditAttribution: merlinofchaos commented 27 May 2010 at 17:03

You may have to write your own views_join handler for that.

Comment #4

iamjon CreditAttribution: iamjon commented 2 November 2010 at 08:20

Status:

Active

» Postponed (maintainer needs more info)

ISPTraderChris,
Were you ever able to solve the problem? If so please share so others can benefit, if not please update so we can better try help you.

Comment #5

iamjon CreditAttribution: iamjon commented 5 December 2010 at 13:04

Status:

Postponed (maintainer needs more info)

» Closed (cannot reproduce)

Comment #6

ufku CreditAttribution: ufku commented 27 July 2017 at 21:52

Version:	6.x-2.6	» 8.x-3.x-dev
Component:	Miscellaneous	» Code
Category:	Support request	» Feature request
Issue summary:	View changes
Status:	Closed (cannot reproduce)	» Active