4

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?

1 Answer 1

3

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.

2
  • Thank you for the detailed reply. I did consider this approach, but I figure it's equally if not more complex than a raw SQL query as I put in the original post - indeed, your solution still involves appending SQL to the query. I had hoped there was a way to do this sort of join with WP_Query itself to avoid all of the complaints from WPCS in my plugin about direct database interactions.
    – Sean
    Apr 7, 2019 at 20:09
  • 1
    @Sean The filters mentioned above are used to expand WP_Query with custom joins and conditions. I'm afraid there is no other way than raw SQL or filters. Anyway, I do not know him. Conditions added to where don't have to be hard coded, they also can be passed as WP_Query parameter like _rev_of_publ.
    – nmr
    Apr 7, 2019 at 20:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.