Alter the moderation state in the Content view Drupal 8

by esod   Last Updated February 28, 2018 21:07 PM

Drupal core content moderation queries the content_moderation_state_field_data table when I add Moderation State to the Content View. That works most of the time, but sometimes moderation states come from the content_moderation_state_field_revision table. For example, let's say there is a state called Request publish configured like this:

Request publish state

There is also a Request publish transition configured like this:

Request publish transition

'Request publish' is used in this manner because some roles can't publish but all roles can 'Request publish'. The problem for the Content view in this context is it doesn't use the right record to show the moderation state. The Content view looks like this:

Wrong moderation state

which is not the right moderation state. When we look at the latest version of the node, the moderation state is listed correctly:

Correct moderation state

Looking at the content_moderation_state_field_revision table we see that the latest revision of id 27 with content_entity_revision_id 89 has a moderation_state of request_publish. This is correct.

Revision table has the correct revision

Unfortunately, the content_moderation_state_field_data table lists id 27 with content_entity_revision_id 88 with a moderation_state of published.

Field data table is not correct

So the Content View is working but the result is not the correct moderation state. This is where hook_views_query_alter() needs to be applied.

For the Moderation state field in this view I need to query the content_entity_revision_id table with a SELECT MAX(id) and GROUP BY (or something to that effect) to get the the most recent moderation state. So far I've only been able to find examples of $query->where in hook_views_query_alter() when what I think I really want to do is alter the LEFT JOIN of the view. Here's my question:

How do I alter the Content view to display the MAX revision moderation_state from the content_moderation_state_field_revision table and not the moderation state from the content_moderation_state_field_data table?

This may be an issue for, but I want to sound it out here first. Also I assume a Views handler is better than hook_views_query_alter().


Related Questions

How to join custom table in views hook_views_data

Updated February 19, 2017 19:07 PM

Bulk delete field collection items

Updated April 29, 2015 21:03 PM

How to use alter in hook_views_query_alter

Updated February 13, 2018 13:07 PM