Views 'Content revision: State' gives wrong results for Pending state

I just ran into this exact issue. I'm trying to figure out how to apply your solution to the source.

It's a hack, but....

In revisioning/views/revisioning_handler_filter_revision_state.inc

I made this change:

        case REVISION_PENDING:
          $subclauses[] = "($revisions_table.vid>$node_table.vid OR ($node_table.status=0 AND (SELECT COUNT(vid) FROM {" . $revisions_table . "} WHERE nid=$node_table.nid)=1))";
          $this->query->table_queue['node_node_revision']['join']->field = 'nid';
          $this->query->table_queue['node_node_revision']['join']->left_field = 'nid';
          break;

It basically does exactly what you said needs to change. I can't find the correct place to make this change, but this fixed it for me. Hopefully a developer of this module can make a better and more permanent solution.

Here's the patch that seems to work. I'm still not sure if it's the best way, but I can't break it.

Nice simple patch...
Great work!
Rik

The proposed fix in #3 caused a WSOD on my system.
In the spirit of the fix in #3 I used:

          $this->query->table_queue[$revisions_table]['join'] = new views_join();
          $this->query->table_queue[$revisions_table]['join']->construct($node_table, $revisions_table, 'nid', 'nid');

This seems to work.
I will adjust the new canned View /content-revisions-summary so that it adds the UNIQUE qualifier which is necessary to get the right results.
Rik

If I make the change from #6 (i.e. just adding those two lines after '$subclauses=...' in the REVISIONING_PENDING case statement), I get a very long list of items, which doesn't correspond to the list of 5 pending items shown in the dashboard. Also, sorting on Content Revision: Updated Date causes an SQLSTATE error, so I had to remove that sort criterion.

I'm using my own custom view (the same as shown in https://drupal.org/node/1430352#comment-7961249, but with the sort criterion removed), because I can't find the canned view mentioned in https://drupal.org/node/1430352 in my list of disabled views.

@highfellow, #8:

Did you run /update.php or at least clear the caches after you downloaded the latest 7.x-1.x-dev version of Revisioning?

As mentioned in #7 not only does the code need to change, but also the advanced settings: DISTINCT needs to be switched on.

The new canned View has that built in.

After your comments I double-checked the sorting. In the very latest canned View (snapshot 21 Oct) I added column sorting to all columns, including Updated Date.
These all work fine. However the State sort is broken. Will fix.

See attached image of new Views available under Structure >> Views.

Rik

I did run update.php, but I noticed just now that there was a mix-up with an older file when I tried to download the new .tar.gz dev release. I've now downloaded the right file. (I checked that the changes from this issue were in there).

I cloned the revisioning_content_summary view to produce something which works for me (I just wanted to filter on revision state plus an extra checkbox field I've added to the content to mark it as ready for publication)

Thanks for your help.

Great. Then all that remains is fix the SORT for the State field.

Yes, the patch of number #6 has been committed and is available in Revisioning 7.x-1.6

Hi, sorry to have to reopen this, but I am getting errors on my views which use the 'revision NID of the content revision' contextual filter. On such views, an additional line is added to the sql query:

LEFT JOIN {node_node_revision} node_revision_2 ON node_revision.nid = node_revision.nid

causing this error:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'drupal7_test.node_node_revision' doesn't exist

I've attached a patch to check that this contextual filter/relationship does not exist before adding the new joins from the above commited patch.

Great pick-up Victioria!
Thanks so much for the patch.
Hope to apply soon.
Rik

EDIT: maybe that test should be like this:

  if (empty($this->query->relationships[$this->relationship])) {
    ...
  }

This is because node and node_revision tables may have aliases....
I've checked this in tentatively.
Can you please test?

I can confirm that patch #16 works on version 7.x-1.6 of the revisioning module.

@teknocat, #18:
Can you confirm that the latest 7.x-1.x-dev works (without the patch), as I've put in the modified patch from #17.
If both #16 and #17 (i.e. the latest 7.x-1.x-dev) work, then I'd feel more comfortable including this in the next official release.
Cheers!
Rik

Is this issue supposed to be fixed in release 7.x-1.7? Because that's the version that I have installed, and I'm seeing exactly the same symptom that's reported in #16.

@Andrew Shulman:
Please try the following in Revisioning 7.x-1.7.
In the file revisioning/views/revisionioning_handler_filter_revision_state.inc, change line 50 from

            if (empty($this->query->relationships[$this->relationship])) {

to

            if (empty($this->query->relationships['node_node_revision'])) {

Does that make any difference?
Rik

This is mostly working for me now, but there still seems to be a small problem. If you create a node, publish it, then add an edit and unpublish the current revision, the new revision isn't picked up by the filter. In other words, if you have one archived revision, and one draft revision, the draft revision doesn't appear in the view. In my case, this would mean that it would be missed by the moderation team and not published.

The corresponding use case is where a user creates a node and it's published, but the moderators then decide to revoke that version. The user creates a new revision, but the moderators never find out about it because it doesn't appear in the dashboard.

Thanks @highfellow
Can you please quote version number and which patch you applied?
Rik

I just updated to the latest version (7.x-1.7) with no patches, and this is still happening.

@highfellow, #26:
So could you please make the one-line change of #23 and report back to this issue queue whether that changes anything for you?
Rik

I've made that change, and it's still doing the same thing.

Ok, then it seems that the new code is an improvement in that it fixes some issues, as in #18 (#16), but not all.
Improved patches welcome.

I suspect that the solution may be to change line 47 in revisioning/views/revisionioning_handler_filter_revision_state.inc either to:

$subclauses[] = "($revisions_table.vid>$node_table.vid OR ($node_table.status=0 AND (SELECT COUNT(vid) FROM {" . $revisions_table . "} WHERE nid=$node_table.nid)>=1))";

or to something like:

$subclauses[] = "($revisions_table.vid>$node_table.vid OR ($node_table.status=0 AND (SELECT COUNT(vid) FROM {" . $revisions_table . "} WHERE vid > $node_table.vid AND nid=$node_table.nid)>=1))";

However, I've been having problems testing this, as drupal isn't recognising the changes I'm making to that file. E.g. I tried changing the WHERE clause for REVISION_PENDING on line 47, but this was not reflected in the query SQL shown on the edit view page. Doing 'drush cc all' has no effect on this. I've also tried disabling and re-enabling the revisioning module.

I guess this is some kind of code caching issue, but can't find a solution elsewhere. If you have any suggestions, that would be good.

@highfellow, #30:
Thank you for your efforts.
That change should be effective immediately, even without clearing all caches.
Views does not always display ALL of the executed queries.
To ascertain that the modified code gets run, you could try inserting drupal_set_message("Hello!"); near the modified section of the code?
Rik

It looks I was either editing the wrong file, or I had my view set up wrong. If I set the view to filter on the revision state instead of the node state, the code is recognised and the message appears. However, I then get a huge (hundreds) list of revision titles with many duplicate rows.

What I want is a view which returns a list of content item titles where:

- there is at least one pending revision (not archived or current).
- a field on the base node called 'ready for publication' is set.

Could you give me some advice on how to construct this?

I've gone back to my original view (with the filter on the node state), and it looks like the answer is to change line 41 in views/revisioning_handler_filter_node_state.inc to:

$subclauses[] = "($node_table.vid<(SELECT MAX(vid) FROM $revisions_table WHERE nid=$node_table.nid) OR ($node_table.status=0 AND (SELECT COUNT(vid) FROM $revisions_table WHERE nid=$node_table.nid)=1))";

The logic for this is that is doesn't matter whether the node is published (current) or not (archived), as long as there are outstanding revisions to be looked at which are newer than the one in the node table.

Having made this change, the missing items (with an archived node revision) appear, and the other results in the view are as before, which solves my issue.

Incidentally, this issue looks like a duplicate of https://drupal.org/node/2059673 if you want to mark that resolved.

(n.b. in the above comment, I'm talking about a different file from my previous comments. I.e. the one for the node state rather than the revision state.)

@RdeBoer, re #23: Yes, the change you suggested there fixes the error in my view.

Unfortunately #23 does not fix the problem for me. It does take away the error message 'SQLSTATE[42S02]: Base table or view not found: 1146 Table 'xxx.node_node_revision' doesn't exist;' yet I only get a subset of the result I should be getting.

What did work for me though was to comment out lines 25 - 27 of revisioning_handler_filter_revision_state.inc

<?php
//  if (!$node_table) {
  //  $node_table = $this->query->ensure_table('node_' . $revisions_table);
    //}
?>

and set distinct on my query settings. Yuk!

CORRECTION: I have just cloned the view 'Content summary (all revisions)' and used that as a base and now it seems fine (without any code alterations). The one thing that still is odd is that when I change the filter logic to

Is not one of: Archive, Current, published' (which I believe is similar to 'is one of : Pending'), I get incorrect results.

With regards #23, I've discovered this change from $this->relationship to node_node_revision is necessary too. Posting the patch here for use with drush make.

This is still an issue. I personally have a View with a relationship to the content revisions, which means the join is already present. The following patch is similar to xcessions's one in #37, but it checks if the $node_table variable is already used in a relationship, instead of hard coding the table name.

It is reasonable to expect that, if the $node_table is already used in a join, it is not necessary to add a new one.

Bump.

We are about to release a new version of a module that depends on this patch. Are there any plans to make a new release soon with this patch included? Our module currently ships with the patch and instructions to apply it, but it would be easier for (both) our users if the Revisioning module simply included it.

@wadmiraal

You mean an official release?
Possibly.
Have just applied a couple of other (unrelated) patches too. But would ideally like to include a few more.

Yes, an official release is what I was thinking about :-). Thanks for committing the patch.