I have a second database whose tables I need to expose to Views for sorting and display. Some of the relationships between these tables are indirect... that is, there is an intermediate linking table that holds the many-to-many relationships between one table and another, like so:
MainTable <--leftjoin <-- LinkingTable <--leftjoin <-- SecondaryTable
Where MainTable could be courses, and the SecondaryTable faculty, for instance.
Now, I know I can build a custom module that describes the tables to Views and also sets up the joins, as per this bit from the Views Advanced Help:
$data['term_data']['table']['join']['node'] = array(
'left_table' => 'term_node',
'left_field' => 'tid',
'field' => 'tid',
);
...which is in fact exactly how Drupal connects nodes to terms through the term_node linking table.
But I was hoping to get either the Table Wizard or Data module to do the descriptive work for me, as per the support request I've made here: http://drupal.org/node/897908 and here: http://drupal.org/node/897814. Unfortunately, while both modules enable the description of simple joins (one table to another), neither seems to permit the setup of complex joins such as I have described. Setting up a join of A to B, then B to C, does not do it. Using "relationships" (manual joining as TW describes it) just makes things stranger.
Does anyone have direct experience with this kind of thing? Can I let these modules do the main data descriptions for Views, but then write my own join descriptions in a custom module? Or do I just need to abandon them altogether and just write the custom module as I should have, from the beginning?
