'Node revision: latest' filter in views

Thanks benanne!
Well done for a first attempt!
Will have a closer look at your patch and see if we can get it in the next release.

I've implemented this patch, thanks benanne! The only thing is you don't explicitly reference the revisions_table nid in your join, my query function looks like this:

  /**
   * Override the query, in particular the WHERE clause.
   */
  function query() {
    $revisions_table = $this->ensure_my_table();
    $node_table = $this->query->ensure_table('node');
    
    $max_vid_subquery = "SELECT MAX(vid) FROM {" . $revisions_table . "} WHERE $revisions_table.nid = $node_table.nid";
    // The subquery selects the maximal revision ID (vid) for a given node ID (nid).
    // This is guaranteed to be the latest revision, because vids are assigned chronologically.
    
    $where_clause = "$revisions_table.vid = ($max_vid_subquery)";
    $this->query->add_where($this->options['group'], $where_clause);
  }

Note to self: commit this patch to both 6.x-3.x and 7.x-1.x (with minor mods).

Committed with minor mod to Git repository, branch 6.x-3.x.
Will be available in 6.x-3.13

Hi there,

I was after a very similar piece of functionality, I was after a content revisions view to have a filter of 'last published revision', so that the file attachments of only the published revision as displayed (and not the revision in draft).

I have ported the above code to Views 3 on drupal 7.x (some slight changes needed to be made), and have made another filter handler for filtering by published revisions only.

Anyone care to review this patch?

Thanks
Sean

I've been having performance problems with the max revisions patch, I've got around 3650 node revisions and my query (which admittedly does have a lot of joins) was taking an unacceptable amount of time, around 51200ms on my remote server. In case anybody else gets this problem, I found it to be much more efficient to restructure the views query so that it added the max node revisions vid as a join instead like this:

SELECT 
// Select fields
FROM node_revisions node_revisions 
// Other joins
LEFT JOIN (SELECT max(nr.vid) as vid FROM node_revisions nr GROUP BY nr.nid ) nr ON node_revisions.vid = nr.vid 
WHERE nr.vid is not null
// My other where clauses 
GROUP BY vid 
// Order by, etc

In order to achieve this effect, I removed the Node Revisions:Latest filter (because I couldn't work out how to achieve the same effect using a custom filter) and instead did the following in a module:

/** 
 * Implementation of hook_views_query_alter().
 */
function mymodule_views_query_alter(&$view, &$query) {
	if($view->name != 'my_content') 
		return;
	
	$subquery = '(SELECT max(nr.vid) as vid FROM node_revisions nr GROUP BY nr.nid )';
	$join = new views_join('node_revisions', 'node_revisions', 'vid', 'vid');
	$join->definition = array('table' => $subquery, 'left_field' => 'vid', 'field' => 'vid', 'left_table' => 'node_revisions');
	$join->extra_type = 'AND';
	$join->table = $subquery;
	$join->left_field = 'node_revisions.vid';
	$join->field = 'vid';
	$join->type = 'LEFT';
	$join->adjusted = true;
	
	$query->table_queue['nr'] = array(
		'table' => $subquery, 
		'alias' => 'nr', 
		'num' => 1, 
		'join' => $join,
		'relationship' => 'node_revisions');
	$query->where[2] = array(
		'clauses' => array('nr.vid is not null'),
		'args' => array(),
		'type' => 'AND');
} 

It's a less pretty solution but I am now finding it takes around 264.42ms to execute.

Just thinking about this a bit, was there a reason why you couldn't use node instead of node_revisions as a type/base table when you created your view? Because if you use node I think that it automatically uses the current published revision.

Just thinking about this a bit, was there a reason why you couldn't use node instead of node_revisions as a type/base table when you created your view? Because if you use node I think that it automatically uses the current published revision.

@rukaya No, if you use node as your base table, I believe you loose the ability to filter by latest vid.

Our situation:

We wanted to display the file attachment(s) for the current published revision of a node on a page using views.

Our old view:

Used 'node' base table, and pulled out the file attachments for the node, it pulled out ALL attachments to ALL revisions regardless if they came from a current or a historic revision. Less than ideal

Our new view:

Uses 'node_revision' as a base table, and uses my newly created Views 3 filter handlers to find the last published (i.e. not draft) revision vid, and then join 'node' and 'file' from there. Much cleaner from our perspective.

I will say that I have not performance tested my patch, and optimisations could potentially be made (open to suggestions)

In case anyone is wondering, this is out SQL that views executes when using my new filter

SELECT file_managed_field_revision_field_file_attachments.filename AS file_managed_field_revision_field_file_attachments_filename, file_managed_field_revision_field_file_attachments.filesize AS file_managed_field_revision_field_file_attachments_filesize
FROM 
node_revision node_revision
LEFT JOIN field_revision_field_file_attachments field_revision_field_file_attachments ON node_revision.vid = field_revision_field_file_attachments.revision_id AND (field_revision_field_file_attachments.entity_type = 'node' AND field_revision_field_file_attachments.deleted = '0')
INNER JOIN file_managed file_managed_field_revision_field_file_attachments ON field_revision_field_file_attachments.field_file_attachments_fid = file_managed_field_revision_field_file_attachments.fid
LEFT JOIN node node ON node_revision.nid = node.nid
WHERE (( (node.type IN  ('page', 'publication')) AND (node_revision.vid = (SELECT MAX(vid) FROM node_revision WHERE node_revision.nid = node.nid AND node_revision.status=1)) AND (node.nid = '563' ) ));

And after flushing the query cache, and running this query manually in mysql, it takes 0.00 seconds ;) Note the last line

AND (node_revision.vid = (SELECT MAX(vid) FROM node_revision WHERE node_revision.nid = node.nid AND node_revision.status=1))

As this is what the filter adds in

Ah, I see, yes that's not very good. Strange because I've got some views with base table node and they have multiple revisions (and I am using revision moderation) and it just seems to automatically show the current published revision for both cck text fields and the cck image fields I've got. Perhaps it's different with files, although I don't really see why it should be. Oh, but I see you're using views 3 and Drupal 7, and I'm still on 6, so that's probably got something to do with it. I wonder whether it's like that by design or if it's a bug in the new views though - I can't see anything in the issues list but it does seem like it's a bit of an odd way for it to work...

Still keen to hear from @RdeBoer to see if my patch is able to be contributed to 7.x-1.x of revisioning (even if it needs tweaks)

@wiifm

I appied your patch cleanly to my revisioning install, and when I try to add the latest filter, I get a broken/missing handler.

@Paul Kim Consulting

Did you do a drush cc all? This is required for views to pick up the new includes from the info file.

@wiifm, #15:
Thanks so much for the patch! Will include in the next batch of fixes and features I'll apply to the 7.x branch.
Rik

Thanks @RdeBoer,

Good to hear this patch will be accepted. Hate running forked code ;)

Thanks
Sean

Will go in this week!

*\o/*

Ok patch from #8 is in, credited to wiifm.
One thing you may want to have a look at: the 2 new View filters "Content revision: Latest" and "Content revision: Latest published" don't seem to want to... err... expose themselves (optional tick-box in Views as you define them).
In fact this leads to errors being displayed.

when I use the filter Content revision: Latest, the handler shows "missing/broken handler"

Any idea ?

thanks
Keith

What version of Revisioning and what version of Views?

My setup: Drupal 7.19 / IIS 7.5 / SQL Server 2008 / Revisioning Version: 7.x-1.4 / Views Version: 7.x-3.5

My issue:
I'm having the same issue as stated in this original issue report:

1. I created a content type named "Document" for files that contains a CKK file field along with other stuff (i.e. title, description, etc.).
2. I created a view to display these Documents
3. I just updated one of the Document nodes with a newer file and a revision was created. But now, both revisions showed up in the view list of Documents.
4. I then tried to add another filter with "Content revision: Latest", and selected the "Latest only" radiobutton. But both files still show up.
5. For a workaround, I ended up adding the following filter: "File: Path Does not contain '_0'" so that files with "_0" in their file path don't show up, but this is only a temporary fix.

Does anyone have any suggestions on how I can just show the latest revision of a content type containing a CCK file?

This code is very close to solving the problem. I think the issue is that the code works for a "node" view and not for a "node_revision" view, and you need the latter to get at unpublished revisions of a published node.

The issue is caused by the fact that the code assumes the node table has an alias of node but if you are using a node_revision view it will get a different alias. In my case node_node_revision. I have attached a patch that fixes it for me but I don't know if it's a legitimate approach.

I cannot use a node view because I need access to revisions other than the published one in the case where the node is actually published.

If it makes more sense for me to start a new issue rather than continue on this feature enhancement let me know and I can.

Patch from #28 applied with attribution to pgillis. Thanks Pete!