Reverse Relationships

Yes please!

Unfortunately, this sounds easy from one perspective, but internally it is not easy. Let's take a simple node reference relationship. From the perspective of the relationship, it binds a node to another node. However, there's an interim table involved, let's call it content_nodetype.

The relationship exists, physically, on the content_nodetype table. The content_nodetype table has an implicit JOIN to the node table, so when added it automatically knows what to do. When the relationship is added, content_nodetype is added through the normal mechanism, and then a relationship (join) is formed between content_nodetype and node which creates the new relationship.

So you get:
node -[implicit]-> content_nodetype -[explicit]-> referenced_node

However, for the reverse relationship, from Views perspective, it would have to live on the 'node' table, and join in the content_nodetype. However, this is awkward, because relationships must link to other base tables, and content_nodetype is not a base table. This means the reverse relationship has to do *two* joins: referenced_node -> content_nodetype, which is NOT implicit like in the forward relationship, and then content_nodetype -> node, which is only implicit going the other direction.

So you get:
referenced_node -[explicit]-> content_nodetype -[explicit]-> related_node

If that made sense to you, you should see that the two relationships are not simple reversals of each other, simply due to the implicit/explicit nature of the joins. And because they're not simple reversals, it is a difficult task to do this automatically because you have to sit down and figure out ALL the possibilities (and I have not done this) and account for them.

Using the hook_views_query_alter you can switch around the tables and remove the one that's unnecessary. It looks a bit ugly , but it works, at least with the cck node reference field. Here's what the important part looks like:

$relationship_join = $query -> table_queue['node_node_data_field_referenced_node']['join'];
$relationship_join -> table = 'content_type_referencing_node';
$relationship_join -> field = 'field_referenced_node_nid';
$relationship_join -> left_table = 'node';
$relationship_join -> left_field = 'nid';

I hope this wasn't too obscure , I just lifted it out of my production code and changed the variable names so it'll be clearer. I just wanted to demonstrate two things:

1. Its possible to alter the query using these lines of code if any of you are really eager to get this particular functionality.
2. It should probably be possible to add this feature eventually.

Im not expecting anything though, I`m grateful for Views and the hours of work it saves me almost every day.

Just wanted to share my method of working around this issue.

Hmmm..... I'll have to try this. Subscribing.....

You may try to use: http://drupal.org/project/cnr
I've seen as well nodereferrer_create module, that can create list of reversed Relationships on node view.

Other related topics:
#318096: no 'create related content" tab when using a view

But anyway, if it's possible, it's should should be done.
I know that's not easy, but maybe we could find the way how to do that in easy way.

It could be implemented in Views as additional option: Reverse Relationship
It will build query with empty value and this value could be proper filled by using hook_views_pre_render()
By loading nodes and comparing which nodes have reverse relationship.
If this can't be done in Views, additional module could extend that functionality.

Example reverse relationship done for my own purpose:

function sgn($x) {
    return (int)((abs($x)-$x)?-1:$x>0);
}

function foo_views_sort($a, $b) {
    $fieldname = 'node_data_field_reverseCT_datetime_field_reverseCT_datetime_value';
    return sgn(strtotime($a->$fieldname) - strtotime($b->$fieldname)) * ( (int)($_GET['sort'] == 'desc') ? -1 : 1 );
}

/**
* Implementation of hook_views_pre_render()
*/
function foo_views_pre_render(&$view) {
    $view_fieldname = 'node_data_field_reverseCT_datetime_field_reverseCT_datetime_value';
    $fieldname = 'field_reverseCT_datetime_value';
    foreach ($view->result as $id => $foo) {
        $rows = array();
        $q = db_query('SELECT * FROM {content_type_reverseCT} WHERE field_ref_node_foo_nid = ' . $foo->nid);
        if (empty($q)) continue;
        while ($arr = db_fetch_array($q)) {
          $rows[] = array((strtotime($arr[$fieldname] . ' UTC')-time()),$arr[$fieldname]); // FIXME: Support for site's time zone instead of UTC
	}
	rsort($rows);
        if (empty($rows[0][0]) || empty($rows[0][1])) continue;
	$foo->$view_fieldname = $rows[0][1];
    }
    if ((!empty($_GET['order'])) && ($_GET['order'] == $fieldname)) usort($view->result, "foo_views_sort");
}

Some of stuff is still hardcoded, so if somebody want to use this code, he need to understand it.
reverseCT is the name of content type which have relation to foo content type. Foo is also the name of module.
This example fill datatime values in existing empty column in our View by checking relationship directly from database (reverseCT content type).
In case of different type of fields, code and db queries could be different.

Is this related to #241078: Reverse node-reference views relationship ?

I think we can get in for node references using nested join. But I've been trying to do it in views and I haven't found the way yet. Any help would be much appreciated.

For example, if we add a noderef field to story type "field_test_noderef" configured to link to pages type, and then we want a view that lists all pages, but also provide information about stories that may or may not link to them, I can do it using phpMyAdmin like this:

SELECT node.nid AS nid,
   node.vid AS vid,
   node.title AS node_title,
   node.type AS node_type,
   node_data_field_test_noderef.nid AS field_nid,
   node_data_field_test_noderef.vid AS field_vid,
   node_data_field_test_noderef.field_test_noderef_nid,
   node_node_data_field_test_noderef.title AS backref_title,
   node_node_data_field_test_noderef.nid AS backref_nid,
   node_node_data_field_test_noderef.vid AS backref_vid,
   node_node_data_field_test_noderef.type AS backref_type
FROM drupal_node node

-- Using INNER in this JOIN when relationship is required.
LEFT JOIN (
   drupal_content_type_story node_data_field_test_noderef
     INNER JOIN drupal_node node_node_data_field_test_noderef
       ON node_data_field_test_noderef.vid = node_node_data_field_test_noderef.vid
) ON node.nid = node_data_field_test_noderef.field_test_noderef_nid

WHERE (node.type in ('page'))

Note how the nested join is made. Usually, noderef relationships append two joins, but like this:

LEFT JOIN drupal_content_type_story node_data_field_test_noderef
  ON node.nid = node_data_field_test_noderef.field_test_noderef_nid
-- Using INNER in this JOIN when relationship is required.
LEFT JOIN drupal_node node_node_data_field_test_noderef
  ON node_data_field_test_noderef.vid = node_node_data_field_test_noderef.vid

But, how can we do nested join in views? I've been trying to play with the relationship handler, but I'm afraid I haven't been able to understand that completely. I think I have to provide a custom views_join handler as well.

Sorry if this isn't related, but maybe it is possible to apply this pattern with nested joins with other types of relationships in views.

For people who are interested in reverse-relationships.
There is as well module which supporting permission based on reverse-references: http://drupal.org/project/nodeaccess_autoreference

any further news on this?

Markus, take a look at how the taxonomy relationship works -- it uses a sub-select which looks similar to the nested join you're using and creates a pretty custom JOIN to do it.

Note: The query difficulties that Markus is experiencing should, I hope, adequately explain why a simple checkbox can never work. IMO they really need to be two different relationships and there is simply no way to make the simple checkbox in the UI work the way people want it to.

Individual reverse relationships will be considered as patches, but a general one simply won't happen.

+1

subscribe