Get only modified posts

Question

I'm using this code for generating a Feed from lasted modified post

mysqli_query( $conn, 
    "SELECT * FROM wp_posts 
     WHERE post_status = 'publish' 
         AND post_type = 'post' 
         AND DATE(post_modified) > DATE(post_date) 
     ORDER BY post_modified DESC 
     LIMIT 50"
);

and it works perfect, now I need to reproduce in a WordPress plugin and I use this code:

 $lastupdated_args = array(
    'paged'               => $paged,
    'orderby'             => 'modified',
    'ignore_sticky_posts' => '1'
);

but in this case it shows all posts ordered my latest modification and not only modified post.

Is it possible to fix?

Answer 1

You can use the posts_where filter:

// Add custom filter
add_filter( 'posts_where', 'wpse_modified' );

// Fetch posts
$query = new WP_Query( $lastupdated_args );

where you can define the filter callback as:

function wpse_modified( $where )
{
   global $wpdb;
   // Run only once:
   remove_filter( current_filter(), __FUNCTION__ );
   // Append custom SQL
   return $where . " AND {$wpdb->posts}.post_modified} != {$wpdb->posts}.post_modified} ';
}

Though it would be handy to be able to use this kind of date queries:

$args = [
    'date_query' => [
        [ 'before' => 'post_modified' ]
    ],
];

and

$args = [
    'date_query' => [
        [ 
            'column'    => 'post_modified_gmt', 
            'after'     => 'post_date_gmt', 
            'inclusive' => false 
        ]
    ],
];

That's maybe an idea for a core ticket! ;-)

Answer 2

$lastupdated_args = array(
    'paged' => $paged,
    'post_status' => 'publish',
    'post_type' = 'post',
    'orderby' => 'modified',
    'ignore_sticky_posts' => '1'
);

I think this will help you.

Answer 3

Maybe something like this will help, i had a situation where i needed to get posts that were modified at least 1 day after they were created:

function dw_get_modified_posts($author, $post_type = 'post') {
    global $wpdb;

    $result = $wpdb->get_results(
        $wpdb->prepare("
            SELECT ID
            FROM `$wpdb->posts`
            WHERE UNIX_TIMESTAMP(post_modified_gmt) - UNIX_TIMESTAMP(post_date_gmt) > 86400
            AND post_type = %s
            AND post_author = %d
        ", $post_type, $author)
    );

    return array_map(function ($post) {
        return $post->ID;

    }, (array) $result);
}

86400 is the 1 day margin which you can edit it, now you can make a wp_query out of it:

$posts_list = new WP_Query([
   'post__in' => dw_get_modified_posts() ?: [0]
]);