Recently, I've been taking my first plunge into the world of custom coding for Drupal Views, with version 3 of the views module and Drupal 6. I've been doing various things such as trying to expose a custom module's database table to views, and establish relationships between my custom table and existing parts of the site in question, and it is this aspect of views that I am going to focus on with this post.

Usually, when setting up a relationship or a join in Views, you will be associating with something like a node or a user, one of the core system elements of Drupal. In this instance, it was a little bit more difficult. My custom module needed to have a relationship with a CCK field, which then (obviously) has a relationship with a node. So it was a three stage relationship (Custom module -> CCK Field -> Node), but the CCK -> Node part would be handled by Drupal for us.

A few things were required to make this work properly:

1) My custom database table had to be declared as a base table for views. A relationship can only be established with a base table, and this is something very important to bear in mind. A snippet of how to do this is included below:

$data['custom_table']['table']['group'] = t('Name of your custom table');
$data['custom_table']['table']['base'] = array(
   'field' => 'your_custom_field',
   'title' => t('Field description'),
 );

2) The required fields from my custom table needed to be exposed using the standard module_views_data hook. There are tons and tons of examples of these so I won't include one for now.

3) We needed to declare a relationship on the CCK field to create our relationship. This was done with a _views_data_alter hook, like so:

/**
 * Implements hook_views_data_alter().
 */
function custom_views_data_alter(&$data) {

  $data['node_data_field_custom_field_name']['field_custom_field
_name_value']['relationship'] = array(
    'base' => 'your_custom_table',
    'base field' => 'your_custom_key_field',
    'relationship field' => 'your_custom_field_for_relationship',
    'handler' => 'views_handler_relationship',
    'label' => t('Description'),
  );
}

4) Be careful with the naming in this hook, I got stuck for ages because I wasn't using the right naming conventions and only cracked it when I went into XDebug on this hook and was able to see all the available field names and the properties of the field, so you may need a PHP debugger of your choice handy for this.

This may not get you exactly to where you want to be but hopefully it will be of some use as I struggled for days with this (including a lot of fruitless googling), so if this helps even one person who accidentally stumbles across this post I'll be very happy.

I've spent a lot of time learning about this, and also once again bugging the guys in the #drupal-views IRC channel, and once again a couple of them must be thanked so a big thank you to damiankloip and dawehner.

P.S - I imagine a fair amount of this if not all is also applicable to Drupal 7, but I haven't tried it out yet.