Views 2 API hook_views_data with more than one table

hey david,

I got the same problem. Can you tell me how you call the jobtrack_ticket fields?

thanx

mrSjoerd: I'm sure you found the answer by now (or gave up), but since I had this exact same problem, I'll post what MY problem was and what the solution is. EDIT: This is only partially working.. the Fields now show up, but I get an SQL error using the second group's fields. I'll try to update this when I figure it out...

First, know that there's almost no validation of your code. If you make a coding error here, you will get no error at all -- you will just get no output, which is quite frustrating but it also means no one can post a single answer to your question. Everyone's problem is specific to their usage, so details are needed.

Here is what worked for me (connecting to 2 corporate dbs in MySQL).

$data = array();

// TABLES / GROUPS USED
$data['Customers']['table']['group'] = t('c.Customers');
$data['CustomerPhones']['table']['group'] = t('c.CustomerPhones');

// TABLE JOINS FOR CORPORATE GO HERE
// join Customers to CustomerPhones
$data['Customers']['table']['join'] = array(
// directly links to CustomerPhones table
'CustomerPhones' => array(
'left_field' => 'Customer_ID',
'field' => 'CustomerID',
),
);

// join CustomerPhones to Customers
$data['CustomerPhones']['table']['join'] = array(
'Customers' => array(
'left_table' => 'Customers',
'field' => 'CustomerID',
),
);

Note: I dropped ['node'] from the join -- unlike David's usage. When I put ['node'] at the end, I get no output.

It can be a bit confusing how some Views documentation and examples are written. Most document how to link into 1 external table, skipping the joins issue completely. A lot of the example docs also fail to identify which field "names" are required to be named exactly as you see, and which field "names" are merely placeholders, to be filled out custom to whatever your table names are.

I found this example MOST helpful: http://svn.civicrm.org/civicrm/trunk/drupal/modules/views/civicrm.views.inc
That link could move in the future, but civicrm.views.inc is nicely written, short, and easy to work out.

Thank you, Dubs.
I cracked my head on how to implement fields from tables further down the hierarchy and you gave me the final idea of how to solve it. Just for clarification I will lay out my database structure and views_data hook (for it may help others to understand it):

Custom module is for storing tour data (i.e. of a band)

Database schema of custom module: Kept pretty simple for demonstration purposes.

CREATE TABLE IF NOT EXISTS `tour_tour` (
  `vid` int(10) unsigned NOT NULL default '0',
  `nid` int(10) unsigned NOT NULL default '0',
  `tour_name` varchar(255) NOT NULL default '',
  `weight` int(11) NOT NULL default '0',
  `updated` int(11) NOT NULL default '0',
  PRIMARY KEY  (`vid`),
  KEY `nid` (`nid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `tour_location` (
  `lid` int(10) unsigned NOT NULL default '0',
 `location_name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`lid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `tour_rel_location` (
  `id` int(10) unsigned NOT NULL default '0',
  `vid` int(10) unsigned NOT NULL default '0',
  `nid` int(10) unsigned NOT NULL default '0',
  `lid` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `lid` (`lid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

So, I have a table "tour_tour" which holds main data for custom node type "tour" which extends "node" table.
In table "tour_location" are all locations of all tours. Each tour can have several locations assigned to it, so there is a relation table "tour_rel_location".

Next, the implementation of hook_views_data:

/*
 * Implements hook_views_data().
 */
function tour_views_data() {
  $data = array();
	
  $data['tour_tour']['table']['group'] = t('Tour module');
  $data['tour_tour']['table']['join'] = array(
    'node_revisions' => array(
      'left_field' => 'vid',
      'field' => 'vid',
    ),
    'node' => array(
      'left_field' => 'vid',
      'field' => 'vid',
    ),
  );
	
  $data['tour_tour']['tour_name'] = array(
    'title' => t('Tour name'),
    'help' => t('Official name of the tour.'),
    'filter' => array(
      'handler' => 'views_handler_filter_state',
    ),
    'field' => array(
      'handler' => 'views_handler_field',
    ),
    'sort' => array(
      'handler' => 'views_handler_sort',
    ),
  );

This connects my main table "tour_tour" to node (revisions) table(s) and defines the tour name as a views field.
Next I want to add views fields for the location and it's id, here's what I did and what works for me like a charm:

$data['tour_rel_location']['table']['group'] = t('Tour module');
	$data['tour_rel_location']['table']['join']['node'] = array(
    'left_table' => 'tour_tour',
      'left_field' => 'vid',
      'field' => 'vid',
  );

	$data['tour_rel_location']['lid'] = array(
    'title' => t('Tour location id'),
    'help' => t('ID of a single location'),
    'field' => array(
      'handler' => 'views_handler_field_numeric',
      'click sortable' => TRUE,
     ),
    'filter' => array(
      'handler' => 'views_handler_filter_numeric',
    ),
    'sort' => array(
      'handler' => 'views_handler_sort',
    ),
  );
	
	$data['tour_location']['table']['group'] = t('Tour module');
	$data['tour_location']['table']['join']['node'] = array(
    'left_table' => 'tour_rel_location',
      'left_field' => 'lid',
      'field' => 'lid',
  );

	$data['tour_location']['location_name'] = array(
    'title' => t('Location name'),
    'help' => t('Name of a location of the tour'),
    'filter' => array(
      'handler' => 'views_handler_filter_state',
    ),
    'field' => array(
      'handler' => 'views_handler_field',
    ),
    'sort' => array(
      'handler' => 'views_handler_sort',
    ),
  );

  return $data;
}

That way, I was able to add even more fields through more table (i.e. tour_sponsor, tour_songs, ...).
By exposing these new views fields, the visitor is able to filter tours by looking for a specific song that has been played or a certain city the tour stopped at or even a local sponsor.

Views is a really powerful module, thanks for that one!

Cheers,
Paul

@sin.star

Please create a new topic. It's easier to handle totally new questions there.

IMHO this ain't a "totally new" question as my problem was thoroughly described by Dubs post. I just wanted to give some more details on how to achieve the connection of joined tables.

If you still think this is something completely different (and you have way more experience than I have) I'm probably still missing the details as I don't see major distinctions and will open a new issue. Any ideas on title which would clarify the variance of this issue's content?

Thanks,
Paul

deleted (double posting)

Oh this wasn't a question sorry.

@sin.star You could have a look at advanced help and try to make it a bit better.