2
  • I have a person table that has a primary key id. This is a base table.
  • I have an address table that has a foreign key person_id. This is NOT a base table (has no primary key)
  • The address table contains a person's address, but not every person has an address.

enter image description here

When describing these two tables to views, how do I set it up so that when the person table is used as a base table, I can optionally add a relationship to address to join in the information?

I know I can add an implicit join on the address definition like so:

$data['address']['table']['join'] = array(
  'person' => array(
    'field' => 'person_id',
    'left_field' => 'id',
  ),
);

This allows me to add any address fields automatically in the Views UI. Views would by default use a LEFT JOIN to bring in the address data. I can change this to an INNER JOIN in the above definition, but I want to allow the user to chose, which is exactly what using a relationship handler does (has the "require this relationship checkbox", which controls INNER vs LEFT).

However, in my analysis of how these handlers work, I don't think I can define the handler in a way that this will work. It seems the relationship handler should be added to the foreign key on the base table. My base table is person, which does not have the foreign key.

For instance, I tried this:

$data['address']['person_id']['relationship'] = array(
  'base' => 'person',
  'base field' => 'id',
  'handler' => 'views_handler_relationship',
  'label' => t('Address'),
  'help' => t("Has basic information on a person's address.")
);

But this doesn't work, since address is not used as a base table in my View.

How can I do this?

P.S. part of my confusion/trouble stem from my inability to define a table neither as a base table nor an implicit join table, which is what I want to do for address.

UPDATE 1: For a real-world example, look at the table accesslog and how it relates to users. When working with a view w/ users base table, you cannot explicitly add the accesslog relationship, but you can add all the accesslog fields since statistics.views.inc creates the implicit relationship. My schema is similar, but I want to use explicit relationships instead.

1

The very first point is that, the table itself should be defined in order for Views to access it. It's not necessary to define all the fields in the table; just define what you want to access.

You can however, define additional fields that are not in the table. The default convention in the $data array is the table name => column_name but you still can add non-existing keys to the array without Views looking for that field in the query.

Try this:

<?php 

$data['person']['addresses'] = array(
  'title' => 'Addresses',
  'help' => 'Load persons address information',
  'relationship' => array(
     'title' => t('Person\'s address'),
     'base' => 'address', // base table
     'base field' => 'person_id',
     'relationship field' => 'id',
     'handler' => 'views_handler_relationship',
     'label' => t('Address information'),
     'help' => t('Address information'),
  ),
);

You will now see a new relationship, Address, to join the table, along with a checkbox to make it an INNER instead of a default LEFT join.

Similarly, you can add additional fields, arguments and filters without having a real field with the array's key in the table.

  • Thanks! I was able to take your suggestion and modify it so it works properly. I will edit your code sample to reflect that. – Brian Mar 24 '14 at 15:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.