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:
There is also a Request publish transition configured like this:
'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:
which is not the right moderation state. When we look at the latest version of the node, the moderation state is listed correctly:
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.
Unfortunately, the content_moderation_state_field_data table lists id 27 with content_entity_revision_id 88 with a moderation_state of published.
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 drupal.org, but I want to sound it out here first. Also I assume a Views handler is better than hook_views_query_alter().