I've recently had cause to make Views make joins to tables in peculiar ways. Here's some notes on the peculiar things I did with the views_join class to accomplish that.
First of all I'll briefly recap how we define a table to Views. Each item in the $data array returned to hook_view_data() represents all the information about a table. Each key in the array is a field on that table (well, or pseudofield), except for the 'table' key which has the basic data about our table, like this:
$data['my_table'] = array(
// This defines how the table joins back to different bases.
'table' => array(
// How to join back to the base table 'crm_party'.
'crm_party' => array(
'left_field' => 'pid',
'field' => 'pid',
),
),
);
// Now we can add field definitions on this table.
That's the simplest case. It says, 'to join back to {crm_party}, join on the column 'pid' on both tables'. (Note I will say 'column' when I am speaking of the database, and 'field' for Views, though that can mean both a field that you add to the view, and a field on the table that provides filters, sorts, or arguments.)
So adding a field on this table will cause Views to add this join clause to the query:
... JOIN my_table ON crm_party.pid = my_table.pid
We can easily join where the columns have different names, by giving different values for 'field' and 'left_field' in the table definition.
If the join requires conditions, that's where the 'extra' clause comes in, like this:
// How to join back to the base table 'crm_party'.
'crm_party' => array(
'left_field' => 'pid',
'field' => 'pid',
'extra' => 'foo = 42',
),
This now gives us:
... JOIN my_table ON crm_party.pid = my_table.pid AND foo = 42
The 'extra' can also take an array, in which case each item is an array containing field, operator, and value. (If it seems a bit like the Database API, but not quite, that's because all this was introduced in Views 2 on Drupal 6).
// How to join back to the base table 'crm_party'.
'crm_party' => array(
'left_field' => 'pid',
'field' => 'pid',
'extra' => array(
// The 'extra' array is numeric, hence has no keys. This always looks odd to me!
array(
'field' => 'foo',
'value' => 42,
'numeric' => TRUE,
),
),
),
So far, this is all covered in the Advanced Help documentation contained within Views. But for our relationship handler from CRM Parties to attached entities, we needed a condition on the join depending on values selected in the UI. So the 'extra', defined in hook_views_alter(), won't do, as it's not changeable. Or is it?
When a relationship handler is adding itself to the query, the query hasn't been fully built yet. Rather, Views has a views_plugin_query_default object which will eventually be used to make a DatabaseAPI SelectQuery. This means we can actually reach into the table queue and change the definition for any table to the left of us, like this:
// Our relationship handler's query method:
function query() {
// Call our parent query method to set up all our tables and joins.
parent::query();
if ($this->options['main']) {
// This is a little weird.
// We don't add an 'extra' (ie a further join condition) on our
// relationship join, but rather on the join that got us here from
// the {crm_party} table.
// This means reaching into the query object's table queue and fiddling
// with the join object.
// Setting a join handler for the join definition is not useful, as that
// would have no knowledge of the user option set in this relationship
// handler.
// @todo: It might however be cleaner to set one anyway and give it
// a method to add the extra rather than hack the object directly...
$table = $this->table;
$base_join = $this->query->table_queue[$table]['join'];
$base_join->extra = array(array('field' => 'main', 'value' => TRUE));
}
When I wrote those comments in the code last week, I'd found that using a custom join handler isn't useful, because that has no knowledge of the relationship handler's data. However, this week I found myself working on a different case where I did need to find a way to do just that.
This week's problem was how to filter out Drupal Commerce products that are in the current cart, or more generally in any order (and the current cart's order ID can be supplied with a default argument plugin).
It seems a reasonable enough thing to ask of Views, but it's actually pretty complex, as what's in a cart or order is not products but line items, each of which refers to a product with a reference field.
After several failed attempts, I managed to write a query that produces the correct result, but it requires joining to a subquery which itself has the order ID within it (see the issue for gory details).
The first hurdle with this is easy to overcome: there's nothing wrong about telling Views about a table that doesn't exist. This is often done with aliased tables, but in fact it can be totally fictional provided we also provide our own join handler which understands what to do to the query. That can be anything, as long as the SELECT fields we also add make sense. Hence it's fine to do this in hook_views_data():
// Fake table for the 'product is in order' argument, made from a subquery.
$data['commerce_product_commerce_line_item'] = array(
'table' => array(
'group' => 'Commerce Product',
'join' => array(
// Join to the commerce_product base.
'commerce_product' => array(
'left_field' => 'entity_id',
'field' => 'line_item_id',
'handler' => 'views_join_commerce_product_line_item',
),
),
),
);
Our custom join class now has to add the subquery to the view, but it also needs the argument value to do this.
The way I worked around this was to override the ensure_my_table() method in the argument handler. Normally, this calls $this->query->ensure_table() which then creates the join, but ensure_table() can take a join parameter to work with. The overridden version of ensure_my_table() creates the join object, and sets the argument value on it:
function ensure_my_table() {
// Pre-empt views_plugin_query_default::ensure_table() by setting our join up now.
// Argh, hack this in for now. This may mean relationships using this break?
$relationship = 'commerce_product';
// Get a join object for our table.
// This is of class views_join_commerce_product_line_item, which takes
// care of joining to a subquery rather than a table.
$join = $this->query->get_join_data($this->table, $this->query->relationships[$relationship]['base']);
// We add the argument value to the join handler as it needs to use it
// within its subquery.
$join->argument = $this->argument;
This means that in the build_join() method for our custom join handler, views_join_commerce_product_line_item, we can rely on the argument value that the views has received:
function build_join($select_query, $table, $view_query) {
// (snip...) build a SelectQuery object for the subquery
// Set the condition based on the argument value.
$subquery->condition('cli.order_id', $this->argument);
// (snip...)
// Add the join on the subquery.
$select_query->addJoin($this->type, $subquery, $table['alias'], $condition);
The views_join class's build_join() method is where the Views system of building a query is translated into a DatabaseAPI SelectQuery object. Here we build up our own query (and we don't need Views-safe aliases, as it's a completely internal, non-correlated subquery), and pass it in as a join which uses it as a subquery.
It remains only for the argument handler's query() method to add the conditions for its field, using the alias and field names we gave for the subquery.
In conclusion, the data structure Views understands may appear to be a fixed, declared thing, but with a little bit of tweaking the way tables are joined in a Views query can be affected by both site configuration and user input.
