Is it possible to select against a post's parent's fields with WP_Query?

Question

I'd like to query post revisions using WP_Query for display on an admin page, and I want to include pagination parameters.

I have two constraints:

1. I would like to only retrieve revisions for published posts, i.e. revisions whose parent - the post ID for which is given in the post_parent field - contains publish in its post_status field.

2. I would like to ignore the revision autogenerated when a post is published, i.e. the revision whose post_date matches that of the parent.

Normally I would simply filter these revisions out after getting the array of posts, but I want to display these elements in an admin list table with pagination so I'd like to use WP_Query's support for that.

In SQL, I would use an INNER JOIN like this to grab the parent posts:

SELECT * FROM {$wpdb->posts} AS posts
INNER JOIN {$wpdb->posts} AS parent_posts
ON posts.post_parent = parent_posts.ID
WHERE posts.post_type = 'revision'
AND parent_posts.post_status = 'publish'
AND posts.post_date <> parent_posts.post_date

Is this possible to do this using WP_Query?

Answer 1

You should use posts_join and posts_where filters to modify JOIN and WHERE clauses.

add_filter( 'posts_join' , 'se333659_posts_join', 20, 2 );
add_filter( 'posts_where' , 'se333659_posts_where', 20, 2 );

function se333659_posts_join( $join, $query )
{
    global $wpdb;

    if ( isset($query->query_vars['_rev_of_publ']) &&
        $query->query_vars['_rev_of_publ'] == '1' )
    {
        $join .= " LEFT JOIN {$wpdb->posts} AS rev_p ON ({$wpdb->posts}.post_parent = rev_p.ID) ";
    }
    return $join;
}

function se333659_posts_where( $where, $query )
{
    global $wpdb;

    if ( isset($query->query_vars['_rev_of_publ']) &&
        $query->query_vars['_rev_of_publ'] == '1' )
    {
        $where .= ' AND rev_p.post_status = \'publish\' AND '.$wpdb->posts.'.post_date <> rev_p.post_date ';
    }
    return $where;
}

How to use:

Changes in the query will be made after passing the _rev_of_publ parameter (name can be changed) to WP_Query.

$args = [
    'post_type'     => 'revision',
    'post_status'   => 'any',     // default value is 'publish'
    '_rev_of_publ'  => 1,
];
$my_query = new WP_Query( $args );

Here you will find more information about using WP_Query.