Reverse relationship

Not able to test the patch now, but this functionality is badly needed. +1.

Interesting. Changing status and making a mental note to take a peek later (if nobody beats me to it).

subscribe

@michiel, mooi gedaan.

@michielnugter many thanks for the work in putting together this patch! Unfortunately, I may have run into a slight bug: at the moment my view seems to display some information (e.g. the body) from the target node rather than the node related to it.

In my case I have a Visit_Report content type and a Book content type which both share a node reference to a Place content type. I would like to create a view block to display on Place nodes which shows the Visit_Report and Book nodes related to that Place. In my view block, I correctly see the Content Type, and Title of the Visit_Report and Book nodes, however for some strange reason the body of every Visit_Report and Book is actually the body of the target Place node, so I end up with:

Type: Book
Title: Book 1
Place Body

Title: Book 2
Place Body

Type: Visit_Report
Title: Report 1
Place Body

I'll go back and check this with another relationship later just to make sure I'm not seeing things...

It sounds like you only have declared the body once in your view. Try adding the body 2x more, each with one of the reverse_ref-relationships. If you have a tableview, you can put the 3 three bodies in the same column.

@johnv thanks for the suggestion, but I'm afraid that didn't work either. I tried declaring an additional body field (without a reverse reference), but that just meant that I saw the Place body twice for each reverse-reference.

I'm fairly certain that I only want to declare the body once in my view, as the only bodies I want to show in the view are the bodies of the reverse-referenced items.

Content Types & Fields Setup
To make my scenario more clear. I have a single field: NodeReference_to_Place, which I reuse in my Visit_Report, and Book content types. Thus I end up with the following content types and fields

Place Content Type
Field: Place Title
Field: Place Body

Visit_Report Content Type
Field: Visit_Report Title
Field: Visit_Report Body
Field: NodeReference_to_Place

Book Content Type
Field: Book Title
Field: Book Body
Field: NodeReference_to_Place

View Setup
Given that background the intent of my view is to put a block on the Place Content_Type page which shows any Visit_Reports and Books that refer to the place in question. Therefore my view is defined as follows:
Arguments
Node: Nid (This should be the Node ID of the Place)

Relationships
NodeReference_to_Place (The direciton is reversed so this should be anything which points to the Place Node ID argument)

Fields
(NodeReference_to_Place) Node: Type
(NodeReference_to_Place) Node: Title
(NodeReference_to_Place) Fields: body

Results
With those field definitions, I would assume that I should never see the body of the Place node provided as an argument to the view; rather I should only see Book or Visit_Report bodies. Unfortunately, while I see the Book and Visit Report titles, I actually end up seeing the Place body.

I did a bit more experimenting and it appears that at least in the case of the Body Field, the Relationship option doesn't have an effect: regardless of whether I choose "Do not use relationship" or the NodeReference_to_Place I get the same result: the body of the Place.

I set up the fields in my view as follows:
(NodeReference_to_Place) Fields: body
Fields: body

I would have expected to see two different bodies, but I actually end up with the Place body repeated twice instead of the referencing node's body, then the place body.

@zabelc, it migth be a Views/References problem. See this References-issue: http://drupal.org/node/962694#comment-3993508. It states that the latest Views-dev release works file.

@johnv & @michielnugter,

As suggested by johnv, I upgraded my views version to 7.x-3.x-dev (dated 1/29) and reapplied this patch. After that I tested and the correct body is coming through exactly as designed!

@michielnugter, I didn't seem to need to apply the second patch, as node references appear to be working fine I'm on version 7.x-2.x-dev (dated 1/31)

Assign to earl to take a look at it.

I guess this does not work all the time as it should.

Interesting. Coming from #1066398: Reverse entity relationships for Views integration (Entity module).

Didn't assume it would be possible to add reverse relationship information to Views itself.

1. Is it safe for Views to assume that a forward relationship from A.fieldA to B.fieldB works identically backwards?

   The current patch for Entity module checks whether the subject of the relationship is a registered entity type and whether it has a base table before attempting to automatically add a reverse relationship.

2. Views UI requires a label and description for each relationship. An error is shown in the UI if the description is empty.

   This is still a minor remaining @todo for the Entity module patch. Actually, I think that descriptions for simple relationships between entity types could be automated (i.e., "The @relation-table-label of the @base-table-label."), but not sure whether that would work for a completely generic approach though.

@sun, the patch requires all relationships to be set up (manually(?)) by the user, specifying all titles Views requires.

subscribe

Here is a new - heavily reworked - version of the original patch. The old one was broken after upgrading Views from dev-20.1.2011 to dev-23.2.2011. It is still unclear why...
Basically, the new patch swaps the 2 fieldnames in a JOIN when the approriate Relationships-option is set.
It works for every entity (user, node, term) since fieldname 'entity_id' is presumed.
(One of two places does not use adjust_join. This is a pity, would make the patch even smaller.)
PS. michielnugter and johnv work on the same project.

+//BEGIN INSERT Reverse Relation - Patch Synetic Mnu/JVO

Please don't try inserting your company name into code with a patch. After reading this I find myself disinterested in actually looking at the code. :/

Is it safe for Views to assume that a forward relationship from A.fieldA to B.fieldB works identically backwards?

This is not safe. In fact, basic nodereference relationships do not work backward.

Slightly simplified, relationships work like this: They take the last table in the chain that Views knows about, creates a bridge join and uses that to bring in a base table that Views knows about.

The basic relationship looks like this:

node1 -> nodereference -> node2

However, the nodereference record in question always belongs to just one node. That means that it in one direction it looks like this:

[node1 -> nodereference] -> node2

But the other way it looks like this:

node2 -> [nodereference -> node1]

In the first relationship, the relationship adds just one join. From the nodref to node2. But in the reverse, the relationship has to add 2 joins: From node2 to the nodereference field, and from the noderef field to the base table.

In light of that, I don't see how an automated reverse relationship can ever actually work in all cases.

@sun & @merlinofchaos, I've been playing with a version of this patch on a test system for a little while, and I've found it quite useful for creating views which show content related to a given node.

It doesn't automate reverse-relationship choice, rather it adds a checkbox to toggle whether a given relationship is reverse or not. Thus I'm able to pick when to use a relationship or the reverse of it.

#18: Yes, I understand that. It does not invalidate the fact that I'm trying to explain that, internally, the relationship and the reverse relationship are constructed *completely* differently.

You cannot just reverse the join. Simply reversing a single join does not reverse a relationship.

@merlinofchaos, #17, sorry for the lines, but it is an easy way for me to re-find and test/debug the modifications I made. My IDE is very simple, and it facilitates re-applying the code after e.g. Views-update. Surely, it must be removed in the definite code.

I'll try to work out a description of my usecase, perhaps we are mixing definitions here...

@merlinofchaos,
this patch changes the relation
node1 -> [nodereference -> node2]
into:
node1 -> [nodereference <- node2]

Perhaps a 'real-world' example is clearer:
My world contains, teacher, schools and pupils; teachers and pupils are related via schools. The relations are fieldable.
In a 'normal' RDBMS this would compose the following 5 tables:
T = Teacher: nid, name (or uid, both are fieldable entities)
S = School: nid, name
P = Pupil: nid, name (or uid, both are fieldable entities)
TS = teachership: teacher.nid, school.nid, from, to
PS = pupilship; pupil.nid, school.nid, from, to
where the latter two table describe n:m relationships.
Graphically in RDBMS-style: T <- TS -> S <- PS -> P
Now I want to know all the pupils for a teacher. In views I cannot do that (where-ever I start), since you can only go 'right', not 'left'. We need to reverse the relationships t <- ts to t -> ts (and s<-ps to s->ps) in order to reach our goals.
In D7, each single join of two RDBMS tables generates two D7-joins:
The relation teachership -> teacher is:
[node1.nid -> field_data_field_teacher_nid.entity_id] + [field_data_field_teacher_nid.field_teacher_nid_nid -> node2.nid]
The same, but reversed relation teacher <- teachership is:
[node1.nid -> field_data_field_teacher_nid.field_teacher_ref_nid] + [field_data_field_teacher_nid.entity_id -> node2.nid]

The following shows a Views-query displaying teacher.title (=name), teacher.nid, teachership.title, teachership.nid where the 2 fields are swapped, generating the reverse relation:

SELECT node.title AS node_title, node.nid AS nid, 
node_field_data_field_teacher_ref.title AS node_field_data_field_teacher_ref_title, 
node_field_data_field_teacher_ref.nid AS node_field_data_field_teacher_ref_nid, 
FROM  {node} node 
LEFT JOIN {field_data_field_teacher_ref} field_data_field_teacher_ref 
ON node.nid = <b>field_data_field_teacher_ref.field_teacher_ref_nid</b> 
LEFT JOIN {node} node_field_data_field_teacher_ref 
ON <b>field_data_field_teacher_ref.entity_id</b> = node_field_data_field_teacher_nid.ref 
WHERE (node.type IN ('teacher'))

Some (bold?) conclusions (forgive me for getting enthousiastic):
- I understood that this was the original purpose of D6-module reverse_node_reference;
- It works with any fielded entity (bundle), thanks to the new D7-design of table 'field_data_{field_name}' and it's generic use of field 'entity_id' (uid, tid, nid);
- It might not work with programmed relations, or regular tables, but that might be an acceptable limitation of the functionality. Since it is activated by the user itself in design-fase of the view, it can be tested upfront;
- Now Views can use n:m many-to-many filters, making a lot of contribs superfluous!
- View this comment line in plugins/views_plugin_query_default.inc: "// Adjusts the left table for our relationship." Correct me if I'm wrong, but aren't we adjusting the Rigth-hand table of the relationship?
Vriendelijke groeten, John

Please reread #17.

Maybe I can simplify this:

From your perspective, the relationship is [node1] -> [node2]. It looks like you can just reverse it.

From the database perspective, there's an intermediary table. That's the table that contains the nodereference information.

To get from [node1] to [node2] you have to go JOIN through [nodereference], so there are two joins:

[node1] -> [nodereference] -> [node2]

Views knows that [nodereference] is attached to [node1] so when you add a field (in this case, the relationship), Views then simply adds a single join from [nodereference] -> [node2]. [node2] is the base table for the new relationship, and it's done.

Thus, you can kind of note it like this:

{[node1] -> [nodereference]} -> [node2]

The {} indicates what Views already knows about, and the rest is what needs to be added for the relationship.

When reversing this, ownership of the [nodereference] record remains with [node1]. Therefore, all Views knows about when you add the new relationship is [node2]. It has to do it like this:

{[node2]} -> [nodereference] -> [node1]

In order to make this relationship, it has to join [nodereference] which 'belongs' to [node1], and then join [node1] from there. I refer to this as a 'two hop' relationship, since it requires two joins.

For each way, the actual endpoints of the relationship are in different places. For the forward relationship, you start on [nodereference] so from Views' perspective, that's the table the relationship lives on.

But for the reverse relationship, you start on [node2], so that's the table that relationship lives on. Both relationships result in new node base table, but they take different paths to get there.

So again: reversing a relationship is not just reversing a join. Relationship forward: Adds one join. Relationship backward: Adds two joins.

If it helps, in the D6 world, there's the 'node referrer' module that provided the reverse relationship on nodereferences. And we certainly can try to get nodereference to supply a reverse relationship as well.

FYI: #21 and #22 were cross-posted. I only completed #21 after merlin's post of #22.
From your last sentence, I believe we understand eachothers intentions and definitions, and you'll have a closer look?

I'm going to simplify again just to show the actual queries. Let's assume:

Table node has field 'nid'.
Table nr has field 'nid' which is the left node, and 'rnid' which is the right node. Views knows this table joins, normally, via node.nid = nr.nid.
The basic forward relationship adds node2 via nr.rnid = node.nid.

So:

SELECT *
FROM node
LEFT JOIN nr ON nr.nid = node.nid
LEFT JOIN node AS node2 ON nr.rnid = node2.nid

If you simply reverse the second join, you get this:

SELECT *
FROM node
LEFT JOIN nr ON nr.nid = node.nid
LEFT JOIN node AS node2 ON nr.nid = node2.nid -- Note that this is now nr.nid not nr.rnid

Which is to say, you'll join back to your original node table. A properly reversed relationship looks like this:

SELECT *
FROM node
LEFT JOIN nr ON nr.rnid = node.nid -- To properly reverse it, we have to change this join too.
LEFT JOIN node AS node2 ON nr.nid = node2.nid

TLDR, but just want to say:

> Is it safe for Views to assume that a forward relationship from A.fieldA to B.fieldB works identically backwards?

No, it's not.
Here's another reason.

The relationship from Profile2 entity to User is obvious. You get one user for your profile.

The reverse is not true! You get several profiles.

So ideally, you want to explicitly provide the user --> profile relationship, along with a handler that lets you narrow it down by type.

subscribing

subscribe

subscribe

subscribe

I found a shortcoming: The entity with the reversed relationship is not loaded in $data - variable, which you can use when manipulating Views-fields.

Forget my last comment - all $data-data is in.

Hi,
Anything I can do to help test out a dev verison of this? Am relatively new to Drupal, but agree with some of the others here that this feature is badly needed (unless someone might have a better way to do this - and has instructions). Really appreciate all the hard work for this module, thanks!

This certainly would be useful functionality - I have made use of the reverse node references module in D6 a number of times.

#35: This isn't in a dev as it hasn't been committed. However, you can test the patch in #26 if you want.
If that isn't something you're familiar with, you may find the following useful: http://drupal.org/patch.

Thanks for the link to that - probably a little over my head, but useful nonetheless. I guess just a personal +1 for this. Thanks again everyone!

Its pretty straightforward.
1) Download patch
2) Apply patch

Exactly how you apply the patch depends on the patching program you use. Patch or git are common.
http://drupal.org/patch/apply goes into some of the details on this.

Ah great! Yeah that is pretty easy and if I would have just watched the instructional video there... :)

It looks like the dev version of Views has been updated since then, but hopefully this gets committed. Thanks!

With Views3, from April16 (and earlier?), this patch generates the following errors:

1. "Fatal error: Allowed memory size". There is a recursive call to e.g. public function load() in drupal\includes\entity.inc.
This happens if you want to show/edit a display with a reverse relationship. You can avoid this by unsetting this field on the master display, then go to the display. Why would this happen on a display, and not the Master?

2. Notice: Undefined index: {field_name}_term_tid in view->fix_missing_relationships() (line 382 of C:\damp\drupal_7.x-sid_synetic\sites\all\modules\views\includes\view.inc).
This notice appears since the new taxonomy-relation rewrite. Doesn't seem to hurt though.

#26 patch works, fantastic, Thanks guys! Hope to port into views soon: )

sub

Though it is true that relationships cannot just be "reversed" some of the time, but 90% of use cases can easily be supported by this. I don't think its such a crazy idea to say that this is a feature that cannot support every possibility. I would really suggest this go in, as I have not built a site in a long time that didn't need this functionality.

(Though I think the idea of relationships, ie the Relation module, are probably better long term solutions)

The patch didn't seem to work for me with a node reference field. That aside, I agree with zzolo that if this works for a lot of use cases it's worth including with a disclaimer. I haven't had the chance to fully test or review the changes, so I can't comment with a lot of authority but having this capability certainly would be beneficial.

I'd happily echo zzolo in #44 word for word.
That said, if this simply isn't going to get in then it will presumably work just as well in a separate module.

1+

Submarino.

Just tried patch in #26, and I dropped it into my views module directory (7.x-3.0-beta3), but I got a bunch of 'No such file or directory' warnings, in addition to a bunch of trailing whitespace errors. I tried applying with $ git apply -v patch_name.patch.

You can apply the patch using patch -p0 < filename.patch

Hi everyone,
Just a +1 for adding in reverse relationships into the core of the module. Being a relatively new Drupal user, this would be a HUGE help. Thanks for all the hard work!

Patch in #26 is working perfectly for me. This is awesome stuff - please consider committing it, as it makes work with the References module heavenly. (Why is this on 'needs work'?).

Awesome that it works for you! I really hope this gets committed as well.

Beside the fact that this can't work all the time. See previous comments...

+    $t = end($this->table_queue);   // get the last added table

This is really hard to guess what the variable means.

+          $t['join']->definition['field'] = $swapped_field;  // The left_field is the swapped field from the relationship  !!!!!!!!!!!!!!!!!!

The comments are placed above the $code not beside. And the !!!!!!!!!!!!!!!!!!! aren't that cool

+      $t = end($this->table_queue);  //reset cursor

So why do you reset cursor here?

This patch doesn't work.

I tried it with a references.module node reference. The reversed relationship just brings up null data.

@merlinofchaos - I've been using this on a dev site for a couple weeks (for many different views, both user references and node references provided by references module), and it's working perfectly for me.

The reversed relationship will only help if you are, say, displaying nodes that refer back to a given node... they don't work the other way :-/

Might we be able to follow michielnugter's advice concerning the reverse not always working?

To address the issue of a reverse relationship not working all the time, might it be an option to place the `reverse relationship` checkbox under the more fieldset?

Seems like a reasonable compromise to me... it won't always provide any relevant data (but neither would a view set to display only unpublished nodes, if there are no unpublished nodes). (Also, I've been using the patch in #26. Haven't tested #56 at all).

subscribe

Subscribe

subscribe. i need a patch for the latest 7.x-3.x-dev (june 06)

subscribe

While I understand that there are likely to be issues with certain use cases I have test #63 with the following use case and can confirm that it "just works".

Content types
Artist
Artwork
Exhibition

Node references
Artwork->Artist
Artwork->Exhibition

View
Show all Exhibitions for an Artist.
This needs to set the content type filter to Exhibition, create a reverse relationship for Exhibition node id from Artwork. Create a relationship for Artist node id from Artwork.

The most surprising part that worked was the artist relationship which was set to use the reverse relationship.

I have attached both my view definition and the SQL output.

Just hit this problem on a site I'm working on. Subscribing.

Subscribed.

Well, it's working a little better, in that I can successfully chain reverse relationships on node references.

However, it seems to only work on node reference relationships. And of course it would, because it doesnt' change the base tables required or provided by the relationship.

Node reference is an unusual relationship. It is one of the few relationships that will typically produce a relationship of the same base table. That is, if your node reference happens to be on a node.

However, if you put a node ref on a user and reverse that relationship, this fails.
If you try to reverse a term reference relationship on a node, this fails.

I'm pretty sure that this will fail on every relationship that produces a different base table than it requires. There's no way for the UI to understand that the base table it will produce is different. So this checkbox currently has a hard requirement for base table in == base table out. That's a problem.

subscribe

Possibly related:

• #1083902: Backreferencing (listing nodes that reference the target node)
• #1188500: Provide a reverse taxonomy field relationship

#1188500: Provide a reverse taxonomy field relationship is my answer to this. It still means that every relationship has to explicitly provide the reverse reference field. BUT for entities, it makes it relatively easy.

This means we need patches to references.module and other modules that provide entity relationships of this sort. The only other one I know of offhand is commerce and I've already mentioned it to bojanz so hopefully commerce products will get this soon.

yched is very busy, so providing a patch to references.module that can use the technique for node/user references would very likely be gratefully accepted.

Marking this one a dup in favor of #1188500.

@michielnugter - Do you think you'll have time to work on helping yched with a references patch?

subscribing.

Looks like there's a patch in #1083902-17: Backreferencing (listing nodes that reference the target node). I'll be testing that shortly. With that patch, the patches in this issue should be unneeded.

subscribe. i tried #63 solution and it seems it works. i can get data for the reversed reference.