I've been trying to create a view that gets all nodes that are in moderated "draft" state as created by the group content moderation module. The current views content state filter will not work. The module has a patch, but this patch requires giving a user site-wide content revision permissions, and we only want to give those permissions on the group level.
I wish to create my own views filter for nodes in current draft state. I wish to create a filter that can be used with additional filters in a view, rather than a standalone views query. This sql code will do it:
select * from content_moderation_state_field_revision where (content_entity_id, content_entity_revision_id) in ( select content_entity_id, MAX(content_entity_revision_id) from content_moderation_state_field_revision where moderation_state = 'draft' group by content_entity_id )
I can also modify above to filter for a specific nid (in the case of a loop reading nodes).
I understand that one way this can be done is with hook_views_data_alter()
But I can't find an example of using this hook to create a views filter using SQL that I can understand. Could someone help me out here? Thanks!