Join Node Revisions table to Activity2

Ya spotted this. It is because we don't join the node_revisions table. Really straight forward if you want to paste this into activity.views.inc function activity_views_data_alter

$data['node_revisions']['table']['join']['activity'] = array(
    'left_table' => 'activity',
    'left_field' => 'nid',
    'field' => 'nid',
  );

Do be careful with this change as it can produce duplicates if you have revisions turned on. A better join is needed, it needs to go through node and vid where n.nid = nr.nid AND n.vid = nr.vid.

Supporting multiple revisions will probably not be supported

This patch adds a relationship to the node table. Since node revisions are not involved there is no problem with duplicates. This patch was tested locally and provides access to the node fields and filters when the relationship is utilized in views.

ya but just looking at it tells me that hte node body isn't provided. (note the node body changed from table 'node' to table 'node_revisions' in Views 2.6 and onward)

The node body is available to Activity views using Views 2.6 and the patch in #5. Here is a screenshot of the node body field added to an activity view: http://img.skitch.com/20090715-pdiump84iqgjetaby3g74n85d3.png. The rendered view also displays the node body correctly in my local tests. The ability to utilize the node body as a field is preserved despite the views 2.6 change mentioned in #6.

re #6, i haven't tested benroot's patch but i'm thinking that views would be smart enough to build a join path to revisions once it gets to node.

I concur, having this is the right way, as opposed to what we have been doing. This should go in.

Tested that the node body can be joined correctly.

This goes one step further and adds in a User relation—though currently the user is always the node author but I'll address that in the correct issue.

This needs to get rid of the activity_views_data_alter() $data['node']['join'] stuff as well.

This relationship will replace this.

And as a bonus, it can also get rid of the ugly activity_db_rewrite_sql() because I believe the node_access filter will be exposed as well.

Note: just read the patch didn't apply it to my working copy.

Scott Reynolds, the relationships and joins aren't bidirectional so you'll still need the hook_views_data_alter() to get them in there when node is the base table but your comment made me wonder why use a relationship? We might as well just specify it as a join so that you see User and Node without having to jump through another hoop. If you want the node's user then add a relationship.

I'm not sure how activity_db_rewrite_sql() comes into play. I'll have to give that a look.

pribeh, did you clear the views cache before testing it? there's a button to do so under view's tools tab.

pribeh, actually which patch did you try? the one from #12?

http://drupal.org/cvs?commit=264252

Uses relationships so that we can set the proper base table and such. Brings in all the CCK stuff (got rid of that mess) and allows us to remove hook_db_rewrite_sql().

though currently the user is always the node author but I'll address that in the correct issue.

I didn't actually see this at all. It was the proper one.

edit make sure you clear your Views cache