Allow joins on many fields - make fields in join config an array instead of 2 strings

Problem/Motivation

Currently views table definitions can only establish joins between tables using a single key pair. They should however also support table joins that require composite keys (a primary key formed by two fields).

Assume a table structure where a table called FOO has a composite key of BAZ_PK and FAZ_PK, and a second table 'BAR' relates to entries in FOO using a composite foreign key of BAZ_FK and FAZ_FK in order to join the two tables together properly, the following sql would be required:

select foo.*
from {foo} foo
 inner join {bar} bar on foo.baz_pk=bar.baz_fk AND foo.faz_pk=bar.faz_fk

In that situation, a module developer would not be able to properly declare to view how those tables relate, and data would be mis-reported. This is because the declaration currently would look like this:

  $data['bar']['table']['join']['foo'] = array(
    'left_field' => 'faz_pk',
    'field' => 'faz_fk',
    // no place for second key field
    );

Drupal core & contrib is mostly not affected by this problem due to the fact that drupal coding and database standards largely encourage the disuse of composite keys, but it is not a requirement. Additionally, there are a number of modules that implement the views api on behalf of other applications, and have little to no control over the data structure therein. Since composite keys are a common structure and organizational construct, it is not uncommon to encounter them in the wild.

At present, there is a solution to this that does not involve a change to views core. Simply put modules that need this functionality must create their own join handler that accepts additional join criteria (not the 'extra' field) and build the join manually. The argument made here is that rather than require many modules to implement what is essentially the same custom class that creates their own arbitrary array/data structure within the views data api standard, the views api should support and handle this need natively.

Also note that this is different from the "extra" construct, which is not designed to re-utilize the joined table, and is specifically designed for extremely rare cases of needing fixed value conditions within a join. this for example would not work for a composite key situation:

  $data['bar']['table']['join']['foo'] = array(
    'left_field' => 'faz_pk',
    'field' => 'faz_fk'
    'extra => 'baz_pk=baz_fk'
   );

Proposed resolution

There are a number of ways in which tables can be joined:

1) simple 1 key joins
2) composite key joins
3) logic based joins
4) product joins (also called cross joins)

Items 1 and 2 comprise the vast majority of all joins, and are arguably the only two views can reliably handle without developer intervention. At present, only the first type of join is supported. In order to handle something more complex than a simple key=key structure, views should have the proposed modification. Should this patch move forward, users would still be able to use the 'left_field' and 'field' keys, but they would also have the option of expressing the joins in a more semantic format that lends itself to better declarations in the future:

this would be ideal for composite key declarations:

  $data['bar']['table']['join']['foo'] = array(
    'fields'=>array(
      array('left_field'=>'faz_pk', 'field' => 'faz_fk'),
      array('left_field'=>'baz_pk', 'field' => 'baz_fk'),
    ),
   );

It would also work for single key declarations:

  $data['bar']['table']['join']['foo'] = array(
    'fields'=>array(
      array('left_field'=>'faz_pk', 'field' => 'faz_fk'),
    ),
   );

Should developers opt for the legacy structure instead for single keys, the patch also allows for that:

  $data['bar']['table']['join']['foo'] = array(
    'left_field' => 'faz_pk',
    'field' => 'faz_fk',
   );

Remaining tasks

A test needs to be written that provides a join across two tables using a composite key, and either the sql output is checked to confirm it rendered correctly, or the view is run, and the correct data is output. This, I need help with.

User interface changes

No UI changes

API changes

There is one API addition recommended in this issue. the Addition of a "fields" key to the "join" are of table declarations in views_data(). The proposed structure would look like this:

  $data['bar']['table']['join']['foo'] = array(
    'fields'=>array(
      array('left_field'=>'faz_pk', 'field' => 'faz_fk'),
      array('left_field'=>'baz_pk', 'field' => 'baz_fk'),
    ),
   );

It should become the new recommended way of declaring join fields, but not remove the existing way of using 'left_field' and 'field' like so:

  $data['bar']['table']['join']['foo'] = array(
    'left_field' => 'faz_pk',
    'field' => 'faz_fk',
   );

Original report by @Mołot

General: Some joins should take care about multiple fields.

My use case:
I have non-base table series with composite PK key (idBrand, idSeries), idBrand is also PK of a base table. And I have series_has_language table, that should be joined to series by series' PK, that means 2 fields. Of course my implementation pretty much assures that idSeries will stay unique, but model's logic does not include this restrain and it should be able to model data like that with views.

Patch should be simple - when generating join, wrap the very ON portion with foreach and glue them with AND. In future someone may find legirimate use-cases for other operators too, but for now I think it's perfectly safe to just ignore them.