Drupal Technical

[Drupal] How to add relationship between custom and Users tables in Drupal views

Binny Thomas | | 3 min read

Sometimes working on Drupal Views is very intimidating as this a very complex module
serving a variety of needs. No wonder it is one of top Drupal modules. Once I had a particularly
difficult time trying to figure out how to get a relationship right in Views by trying to get the user
name from the users table using a userid from a field in my custom table. However I managed to
figure out the soution by reading the query generated by Views. Read on to know how to add a relationship
between a custom table and Users table in Views below

Firstly if you want to expose your custom table to views, use the Drupal
hook 

hook_views_data

Checkout the hook in detail at
Expose table to Views hook

Now to define a relationship to a table in this hook use say for the users table
user the following code. Assuming that you have set the table group to a name which in this case
would be Affiliate Users.


  'join' => array(
    'users' => array(
    'left_field' => 'uid',
    'field' => 'refid',
  ),

Here you are indicating that the refid column in your custom field should be joined to the users table on
users.uid.

Once you have done that we need to define a relationship in the same hook. Refer the following code.


'parent' => array(
  'title' => t('Parent'),
  'help' => t('User-id of parent affiliate'),
  'relationship' => array(
  'base' => 'users',
  'base_field' => 'name',
  'field' => 'parent',
  'handler' => 'views_handler_relationship',
  'label' => t('Referring relationship to user'),
),

You are literally stating a relationship for your field parent to the users table. In this example parent is a user
id. I want to get the username from this id. So I set the base key to 'users' which is the users table and since
I need the name, I set the base_field to name'. Next I provided a name for this relationship. Thats all with the
coding part.

Now I need to bring in this relationship into the configuration of view. Proceed to open
the view display where you want to bring this relationship in edit mode. Select Advanced->Relationships
From the list, zero in on the group you have set for your custom table and then zero in on the field name which we
had defined in code. In our case it would (Affliate Users: Parent)

Once you are done with that, in the same page, open Fields->Select Add. In the suggestion list zero in on User:Name as we
need the user name. Select that and in the next prompt in the Relatoinship Field set the Relationship to the name we
had defined in code earlier which would be Referring relationship to user. This is the most important configuration which
everyone is likely to miss. This will retrieve the corresponding users name from the id

This was a very frustrating for me to figure out whicl I was coding but I managed to figure it out from the query. Hope this saves
a lot of fingerbiting and hair pinching for you

 

 

Related Topics