create views filter from sql

gb flag

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!

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.